Help breaking common row value into multiple rows

Transact-SQL
1

I have the following result set stored in a dummytable.

sym		id		    details
ABC		0x00123	    AAAAAAAAAA
ABC		0x00123	    BBBBBBBBBB
EFG		0x00345	    CCCCCCCCCC
HIJ		0x00678	    CCCCCCCCCC
XYZ		0x00900	    XXXXXXXXXX
XYZ		0x00900	    YYYYYYYYYY
XYZ		0x00900     ZZZZZZZZZ

I would like to get break the details table into three columns based on symbol and details, as shown below. I have been trying to work with pivot without success. Any help would be much appreciated!

sym		id		details1	   details2	       details3		
ABC		0x00123	AAAAAAAAAA     BBBBBBBBBB	   null			
EFG		0x00345	CCCCCCCCCC     null	           null			
HIJ		0x00678	CCCCCCCCCC     null		       null			
XYZ		0x00900	XXXXXXXXXX     YYYYYYYYYY      ZZZZZZZZZZ
2

You can use a traditional cross-tab query:

 --==== Sample data
Declare @dummyTable Table (sym char(3), id varchar(7), details varchar(20));

 Insert Into @dummyTable (sym, id, details)
 Values ('ABC', '0x00123', 'AAAAAAAAAA')
      , ('ABC', '0x00123', 'BBBBBBBBBB')
      , ('EFG', '0x00345', 'CCCCCCCCCC')
      , ('HIJ', '0x00678', 'DDDDDDDDDD')
      , ('XYZ', '0x00900', 'XXXXXXXXXX')
      , ('XYZ', '0x00900', 'YYYYYYYYYY')
      , ('XYZ', '0x00900', 'ZZZZZZZZZZ');

 --==== Solution
   With detail_rows
     As (
 Select dt.sym
      , dt.id
      , dt.details
      , row_num = row_number() over(Partition By dt.sym, dt.id Order By dt.details)
   From @dummyTable                     dt
        )
 Select dr.sym
      , dr.id
      , Detail1 = max(Case When dr.row_num = 1 Then dr.details End)
      , Detail2 = max(Case When dr.row_num = 2 Then dr.details End)
      , Detail3 = max(Case When dr.row_num = 3 Then dr.details End)
   From detail_rows                     dr
  Group By
        dr.sym
      , dr.id;

The first thing we need to do is add a row number - this is used to 'sort' the details data within each partition so we can then cross-tab the details.

1 Like
3

Great Jeff, thanks so much!

4

BTW - for future reference see how I provided sample data? If you add that to your posts - you are likely to get a tested and validated answer much quicker than without that data.

1 Like