asked on

Duplicates and Pivot Tables

Hi,

I have a table of rows that I need presented as columns so I decided to try and utilize Pivot tables. Here's the sample source table and the pivot query is below (notice this sample record set has four sets of records -- two of which are duplicates):

table1
Name,Recorded,VarValue
height, 2011-01-18 20:35:00.000, 60
width, ,2011-01-18 20:35:00.000, 160
weight, 2011-01-18 20:35:00.000,180
depth, 2011-01-18 20:35:00.000, 35
temperature, 2011-01-18 20:45:00.000, 98
height, 2011-01-18 20:45:00.000, 65
width, ,2011-01-18 20:45:00.000, 170
weight, 2011-01-18 20:45:00.000,190
depth, 2011-01-18 20:45:00.000, 36
temperature, 2011-01-18 20:45:00.000, 99
height, 2011-01-18 20:35:00.000, 60
width, ,2011-01-18 20:35:00.000, 160
weight, 2011-01-18 20:35:00.000,180
depth, 2011-01-18 20:35:00.000, 35
temperature, 2011-01-18 20:45:00.000, 98
height, 2011-01-18 20:45:00.000, 65
width, ,2011-01-18 20:45:00.000, 170
weight, 2011-01-18 20:45:00.000,190
depth, 2011-01-18 20:45:00.000, 36
temperature, 2011-01-18 20:45:00.000, 99

The issue I'm having is that the duplicates in this record set are not being displayed. The query produces:

Recorded, height, width, weight, depth, temperature
2011-01-18 20:35:00.000, 60, 160, 180, 35, 98
2011-01-18 20:35:00.000, 65, 170, 190, 36, 99

I would like it to include the duplicates which would result in 4 records.

I'm open to any solution to this. It doesn't have to use pivot tables.

Thanks!

select      Recorded, height, width, weight, depth, temperature
from       (select Recorded, NAME, VarValue from dbo.table1) as sourcetable
PIVOT  ( SUM(VarValue) FOR Name IN (height, width, weight, depth, temperature))AS p

Open in new window

Nico Bontenbal

This:

create table table1(Name varchar(20),Recorded datetime, VarValue int)

insert into table1 values ('height', '2011-01-18 20:35:00.000', 60)
insert into table1 values ('width', '2011-01-18 20:35:00.000', 160)
insert into table1 values ('weight', '2011-01-18 20:35:00.000',180)
insert into table1 values ('depth', '2011-01-18 20:35:00.000', 35)
insert into table1 values ('temperature', '2011-01-18 20:35:00.000', 98)
insert into table1 values ('height', '2011-01-18 20:45:00.000', 65)
insert into table1 values ('width', '2011-01-18 20:45:00.000', 170)
insert into table1 values ('weight', '2011-01-18 20:45:00.000',190)
insert into table1 values ('depth', '2011-01-18 20:45:00.000', 36)
insert into table1 values ('temperature', '2011-01-18 20:45:00.000', 99)
insert into table1 values ('height', '2011-01-18 20:35:00.000', 60)
insert into table1 values ('width', '2011-01-18 20:35:00.000', 160)
insert into table1 values ('weight', '2011-01-18 20:35:00.000',180)
insert into table1 values ('depth', '2011-01-18 20:35:00.000', 35)
insert into table1 values ('temperature', '2011-01-18 20:45:00.000', 98)
insert into table1 values ('height', '2011-01-18 20:45:00.000', 65)
insert into table1 values ('width', '2011-01-18 20:45:00.000', 170)
insert into table1 values ('weight', '2011-01-18 20:45:00.000',190)
insert into table1 values ('depth', '2011-01-18 20:45:00.000', 36)
insert into table1 values ('temperature', '2011-01-18 20:45:00.000', 99)

select      Recorded, height, width, weight, depth, temperature
from       (select Recorded, NAME, VarValue from dbo.table1) as sourcetable
PIVOT  ( SUM(VarValue) FOR Name IN (height, width, weight, depth, temperature))AS p

Open in new window

returns:
Recorded      height      width      weight      depth      temperature
2011-01-18 20:35:00.000      120      320      360      70      98
2011-01-18 20:45:00.000      130      340      380      72      296

You need a way to uniquely identify the sets. You might be able to adapt the procedure that fills the table, or run some code to add the setid afterwards. Because when you have something like this:

create table table1(setid int, Name varchar(20),Recorded datetime, VarValue int)

insert into table1 values (1,'height', '2011-01-18 20:35:00.000', 60)
insert into table1 values (1,'width', '2011-01-18 20:35:00.000', 160)
insert into table1 values (1,'weight', '2011-01-18 20:35:00.000',180)
insert into table1 values (1,'depth', '2011-01-18 20:35:00.000', 35)
insert into table1 values (1,'temperature', '2011-01-18 20:35:00.000', 98)
insert into table1 values (2,'height', '2011-01-18 20:45:00.000', 65)
insert into table1 values (2,'width', '2011-01-18 20:45:00.000', 170)
insert into table1 values (2,'weight', '2011-01-18 20:45:00.000',190)
insert into table1 values (2,'depth', '2011-01-18 20:45:00.000', 36)
insert into table1 values (2,'temperature', '2011-01-18 20:45:00.000', 99)
insert into table1 values (3,'height', '2011-01-18 20:35:00.000', 60)
insert into table1 values (3,'width', '2011-01-18 20:35:00.000', 160)
insert into table1 values (3,'weight', '2011-01-18 20:35:00.000',180)
insert into table1 values (3,'depth', '2011-01-18 20:35:00.000', 35)
insert into table1 values (3,'temperature', '2011-01-18 20:35:00.000', 98)
insert into table1 values (4,'height', '2011-01-18 20:45:00.000', 65)
insert into table1 values (4,'width', '2011-01-18 20:45:00.000', 170)
insert into table1 values (4,'weight', '2011-01-18 20:45:00.000',190)
insert into table1 values (4,'depth', '2011-01-18 20:45:00.000', 36)
insert into table1 values (4,'temperature', '2011-01-18 20:45:00.000', 99)

select      Recorded, height, width, weight, depth, temperature
from       (select setid, Recorded, NAME, VarValue from dbo.table1) as sourcetable
PIVOT  ( SUM(VarValue) FOR Name IN (height, width, weight, depth, temperature))AS p

Open in new window

You'll get the right result.

blue44

ASKER

Great example...make senses...you're grouping the records by an id. This record set actually has a primary key but I didn't include it in this example. I'm assuming each record having a unique ID wouldn't make a difference? Is there any other way to display these records as I'd like without using pivot tables? I've actually written a Java routine which will process the data and display it like I need but it's very resource intensive since it loops around these record sets between the app and db server. I was hoping to keep all the processing on SQL Server.

ASKER CERTIFIED SOLUTION

Nico Bontenbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sameer2010

Not sure, but I think, you should get 6 resultsets considering your example. Try this query:

;with ct1 as (select Name,Recorded,VarValue,ROW_NUMBER() over(order by recorded asc) as r from table1 where Name='height')
,ct2 as (select Name,Recorded,VarValue from table1 where Name='width')
,ct3 as (select Name,Recorded,VarValue from table1 where Name='weight')
,ct4 as (select Name,Recorded,VarValue from table1 where Name='depth')
,ct5 as (select Name,Recorded,VarValue from table1 where Name='temperature')
select distinct ct1.Recorded,ct1.r,ct1.VarValue as height,ct2.VarValue as width,ct3.VarValue as weight,ct4.VarValue as depth,ct5.VarValue as temperature
from ct1 left outer join ct2
on ct1.Recorded=ct2.Recorded
left outer join ct3
on ct1.Recorded=ct3.Recorded
left outer join ct4
on ct1.Recorded=ct4.Recorded
left outer join ct5
on ct1.Recorded=ct5.Recorded

Open in new window

blue44

ASKER

@sameer2010 -- that is some true SQL wizidry -- amazing stuff!! The only issue is that your query against my record set of about 300K records has been running for 9 hours so I had to kill it. :-)

blue44

ASKER

OOPS!! I meant to award these points to Nicobo. How do I change this? So sorry...just late and I'm tired.

blue44

ASKER

I apologize for the error in awarding points. :-)