We help IT Professionals succeed at work.

Duplicates and Pivot Tables

Medium Priority
404 Views
Last Modified: 2012-06-21
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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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.
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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

Author

Commented:
@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. :-)  

Author

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

Author

Commented:
I apologize for the error in awarding points. :-)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.