Unpivot data

Question

Unpivot data

GrassHopper

SSCarpal Tunnel

Points: 4473
More actions
April 2, 2019 at 1:08 pm

#2505543

I need help unpivoting this data:
IDHDRIDRIDWMItemItemDescriptionDCPrepaidCollect
113DC/6042 51
413DC/6047 158
713DC/605572
1013DC/60564422
214DC/6042 68
514DC/6047 112
814DC/60552211
1114DC/60563322
315DC/6042 1212
615DC/6047 1510
915DC/60557065
748353570272565OSM SPINACH RAVIOLIDC/607327.2
753353570272565OSM SPINACH RAVIOLIDC/702327.2
758353570272565OSM SPINACH RAVIOLIDC/704827.2
749354570272566OSM CHICKEN SACOTINIDC/607327.2
754354570272566OSM CHICKEN SACOTINIDC/702327.2
759354570272566OSM CHICKEN SACOTINIDC/704827.2
750355570272567OSM 3COLOR TORTELINIDC/607327.2
755355570272567OSM 3COLOR TORTELINIDC/702327.2
760355570272567OSM 3COLOR TORTELINIDC/704827.2
751356570272568OSM BEEF TORTELINIDC/607327.2
756356570272568OSM BEEF TORTELINIDC/702327.2
761356570272568OSM BEEF TORTELINIDC/704827.2
752357570272569OSM PARM PROSCIUTTODC/607327.2
757357570272569OSM PARM PROSCIUTTODC/702327.2
762357570272569OSM PARM PROSCIUTTODC/704827.2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76989 More actions · Answer 1

You've been around long enough to know that that's not the way to present data. You need to supply a script that will create a (temp) table or declare a table variable and an insert statement that will insert your data into said table.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 2

Point taken...please see data below.

Create Table dbo.MyTable(ID int, HdrID int, RID int, WmItem varchar(50), ItemDescription varchar(150), DC varchar(50), Prepaid varchar(50), Collect varchar(50))

Go

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1','1','3','','','DC/6042 ','5','1')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('4','1','3','','','DC/6047 ','15','8')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('7','1','3','','','DC/6055','7','2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('10','1','3','','','DC/6056','44','22')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('2','1','4','','','DC/6042 ','6','8')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('5','1','4','','','DC/6047 ','11','2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('8','1','4','','','DC/6055','22','11')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('11','1','4','','','DC/6056','33','22')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('3','1','5','','','DC/6042 ','12','12')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('6','1','5','','','DC/6047 ','15','10')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('9','1','5','','','DC/6055','70','65')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('748','35','3','570272565','OSM SPINACH RAVIOLI','DC/6073','30','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('753','35','3','570272565','OSM SPINACH RAVIOLI','DC/7023','35','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('758','35','3','570272565','OSM SPINACH RAVIOLI','DC/7048','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('749','35','4','570272566','OSM CHICKEN SACOTINI','DC/6073','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('754','35','4','570272566','OSM CHICKEN SACOTINI','DC/7023','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('759','35','4','570272566','OSM CHICKEN SACOTINI','DC/7048','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('750','35','5','570272567','OSM 3COLOR TORTELINI','DC/6073','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('755','35','5','570272567','OSM 3COLOR TORTELINI','DC/7023','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('760','35','5','570272567','OSM 3COLOR TORTELINI','DC/7048','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('751','35','6','570272568','OSM BEEF TORTELINI','DC/6073','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('756','35','6','570272568','OSM BEEF TORTELINI','DC/7023','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('761','35','6','570272568','OSM BEEF TORTELINI','DC/7048','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('752','35','7','570272569','OSM PARM PROSCIUTTO','DC/6073','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('757','35','7','570272569','OSM PARM PROSCIUTTO','DC/7023','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('762','35','7','570272569','OSM PARM PROSCIUTTO','DC/7048','','27.2')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('855','42','3','554318772','WM BAH SOURDGH BOULE','DC/6042 ','20.09','25')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('860','42','3','554318772','WM BAH SOURDGH BOULE','DC/6047 ','20.09','25')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('865','42','3','554318772','WM BAH SOURDGH BOULE','DC/6055','20.09','30')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('870','42','3','554318772','WM BAH SOURDGH BOULE','DC/6056','20.09','30')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('875','42','3','554318772','WM BAH SOURDGH BOULE','DC/6057','20.09','35')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('880','42','3','554318772','WM BAH SOURDGH BOULE','DC/6059','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('885','42','3','554318772','WM BAH SOURDGH BOULE','DC/6062','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('890','42','3','554318772','WM BAH SOURDGH BOULE','DC/6064','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('895','42','3','554318772','WM BAH SOURDGH BOULE','DC/6065','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('900','42','3','554318772','WM BAH SOURDGH BOULE','DC/6071','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('905','42','3','554318772','WM BAH SOURDGH BOULE','DC/6072','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('910','42','3','554318772','WM BAH SOURDGH BOULE','DC/6073','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('915','42','3','554318772','WM BAH SOURDGH BOULE','DC/6074','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('920','42','3','554318772','WM BAH SOURDGH BOULE','DC/6077','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('925','42','3','554318772','WM BAH SOURDGH BOULE','DC/6082','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('930','42','3','554318772','WM BAH SOURDGH BOULE','DC/6083','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('935','42','3','554318772','WM BAH SOURDGH BOULE','DC/6084','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('940','42','3','554318772','WM BAH SOURDGH BOULE','DC/6085','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('945','42','3','554318772','WM BAH SOURDGH BOULE','DC/6090','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('950','42','3','554318772','WM BAH SOURDGH BOULE','DC/6091','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('955','42','3','554318772','WM BAH SOURDGH BOULE','DC/6095','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('960','42','3','554318772','WM BAH SOURDGH BOULE','DC/6096','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('965','42','3','554318772','WM BAH SOURDGH BOULE','DC/6097','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('970','42','3','554318772','WM BAH SOURDGH BOULE','DC/6099','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('975','42','3','554318772','WM BAH SOURDGH BOULE','DC/7010','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('980','42','3','554318772','WM BAH SOURDGH BOULE','DC/7012','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('985','42','3','554318772','WM BAH SOURDGH BOULE','DC/7013','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('990','42','3','554318772','WM BAH SOURDGH BOULE','DC/7014','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('995','42','3','554318772','WM BAH SOURDGH BOULE','DC/7015','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1000','42','3','554318772','WM BAH SOURDGH BOULE','DC/7016','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1005','42','3','554318772','WM BAH SOURDGH BOULE','DC/7017','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1010','42','3','554318772','WM BAH SOURDGH BOULE','DC/7018','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1015','42','3','554318772','WM BAH SOURDGH BOULE','DC/7019','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1020','42','3','554318772','WM BAH SOURDGH BOULE','DC/7021','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1025','42','3','554318772','WM BAH SOURDGH BOULE','DC/7023','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1030','42','3','554318772','WM BAH SOURDGH BOULE','DC/7024','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1035','42','3','554318772','WM BAH SOURDGH BOULE','DC/7025','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1040','42','3','554318772','WM BAH SOURDGH BOULE','DC/7030','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1045','42','3','554318772','WM BAH SOURDGH BOULE','DC/7048','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1050','42','3','554318772','WM BAH SOURDGH BOULE','DC/7055','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1055','42','3','554318772','WM BAH SOURDGH BOULE','DC/7077','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1060','42','3','554318772','WM BAH SOURDGH BOULE','DC/7084','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1065','42','3','554318772','WM BAH SOURDGH BOULE','DC/8851','20.09','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('856','42','4','550300717','ORGANIC MULTIGRAIN','DC/6042 ','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('861','42','4','550300717','ORGANIC MULTIGRAIN','DC/6047 ','18.72','35')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('866','42','4','550300717','ORGANIC MULTIGRAIN','DC/6055','18.72','30')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('871','42','4','550300717','ORGANIC MULTIGRAIN','DC/6056','18.72','20')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('876','42','4','550300717','ORGANIC MULTIGRAIN','DC/6057','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('881','42','4','550300717','ORGANIC MULTIGRAIN','DC/6059','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('886','42','4','550300717','ORGANIC MULTIGRAIN','DC/6062','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('891','42','4','550300717','ORGANIC MULTIGRAIN','DC/6064','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('896','42','4','550300717','ORGANIC MULTIGRAIN','DC/6065','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('901','42','4','550300717','ORGANIC MULTIGRAIN','DC/6071','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('906','42','4','550300717','ORGANIC MULTIGRAIN','DC/6072','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('911','42','4','550300717','ORGANIC MULTIGRAIN','DC/6073','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('916','42','4','550300717','ORGANIC MULTIGRAIN','DC/6074','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('921','42','4','550300717','ORGANIC MULTIGRAIN','DC/6077','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('926','42','4','550300717','ORGANIC MULTIGRAIN','DC/6082','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('931','42','4','550300717','ORGANIC MULTIGRAIN','DC/6083','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('936','42','4','550300717','ORGANIC MULTIGRAIN','DC/6084','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('941','42','4','550300717','ORGANIC MULTIGRAIN','DC/6085','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('946','42','4','550300717','ORGANIC MULTIGRAIN','DC/6090','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('951','42','4','550300717','ORGANIC MULTIGRAIN','DC/6091','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('956','42','4','550300717','ORGANIC MULTIGRAIN','DC/6095','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('961','42','4','550300717','ORGANIC MULTIGRAIN','DC/6096','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('966','42','4','550300717','ORGANIC MULTIGRAIN','DC/6097','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('971','42','4','550300717','ORGANIC MULTIGRAIN','DC/6099','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('976','42','4','550300717','ORGANIC MULTIGRAIN','DC/7010','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('981','42','4','550300717','ORGANIC MULTIGRAIN','DC/7012','18.72','')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('986','42','4','550300717','ORGANIC MULTIGRAIN','DC/7013','18.72','20')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('991','42','4','550300717','ORGANIC MULTIGRAIN','DC/7014','18.72','20')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('996','42','4','550300717','ORGANIC MULTIGRAIN','DC/7015','18.72','22')

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES ('1001','42','4','550300717','ORGANIC MULTIGRAIN','DC/7016','18.72','25')

drew.allen SSC Guru Points: 76989 More actions · Answer 3

I don't see anything that requires an UNPIVOT. What are your expected results?

Are you trying to create an EAV table? That's highly discouraged.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 4

I wpuld like to get the following columns DC, prepaid, collect, WMItem, ItemDescription on the same row by Hdrid + RID. I'm not sure if this is clear or doable?

drew.allen SSC Guru Points: 76989 More actions · Answer 5

GrassHopper wrote:

I wpuld like to get the following columns DC, prepaid, collect, WMItem, ItemDescription on the same row by Hdrid + RID. I'm not sure if this is clear or doable?

No, it's not at all clear. The data is already in that format. The best thing to do is script up the expected results in exactly the same way that you scripted the sample data.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA