[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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

0
blue44
Asked:
blue44
  • 4
  • 2
1 Solution
 
Nico BontenbalCommented:
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.
0
 
blue44Author 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.
0
 
Nico BontenbalCommented:
The primary key is unique for each record. You need something that is unique for each set. But you can do the looping and the processing on SQL server too. Use a temp table and a cursor. This piece of code assumes that each set ends with a temperature record. You might need to adapt it for your specific needs:
create table table1(id int IDENTITY (1, 1), 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: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)



--create a temp table to hold the result
create table #result(Recorded datetime, Name varchar(20), Height int, Width int, Weight int, Depth int, Temperature int)

--declare a cursor to loop through the data
declare @Recorded datetime, @Name varchar(20), @VarValue int
declare @Height int, @Width int, @Weight int, @Depth int, @Temperature int
DECLARE cur CURSOR FOR SELECT Recorded, Name, VarValue from table1 order by id
OPEN cur;

FETCH NEXT FROM cur INTO @Recorded, @Name, @VarValue

WHILE @@FETCH_STATUS = 0
BEGIN
    if @Name = 'height' set @Height = @VarValue 
    if @Name = 'width' set @Width = @VarValue 
    if @Name = 'weight' set @Weight = @VarValue 
    if @Name = 'depth' set @Depth = @VarValue 
    if @Name = 'temperature' 
    begin
        set @Temperature = @VarValue 
        insert into #result values (@Recorded, @Name, @Height,@Width, @Weight, @Depth, @Temperature)
    end
    FETCH NEXT FROM cur INTO @Recorded, @Name, @VarValue
END
CLOSE cur;
DEALLOCATE cur;

--retun the result
select * from #result 

drop table #result

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
sameer2010Commented:
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

0
 
blue44Author 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. :-)  
0
 
blue44Author Commented:
OOPS!! I meant to award these points to Nicobo.  How do I change this? So sorry...just late and I'm tired.
0
 
blue44Author Commented:
I apologize for the error in awarding points. :-)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now