Convert SQL rows into columns based on separate blocks within the results

Question

I wonder how I might take rows from a result set and turn those rows into columns of a new table - but there is a twist - I would require the individual row items to be converted into one row of data, so that the new table would only have one row for item 3, one row for item 4 etc - but - when a new item 3 is found in the results I need to have that as a separate row in the new table.

I am using SQL Server 2012 Express. Start Date and End Date are datetime, Item is varchar(256) and Code is varchar(256):

Sample data:

 Start Date            End Date              Item   Code  
*2010-01-01 04:30:00      2010-01-01 04:33:35   3      1
*2010-01-01 04:33:36      2010-01-01 04:37:11   3      3
*2010-01-01 04:37:12      2010-01-01 04:40:47   3      4
 2010-01-01 06:43:12      2010-01-01 06:46:47   4      2
 2010-01-01 06:46:48      2010-01-01 06:50:23   4      3
 2010-01-01 08:56:24      2010-01-01 08:59:59   5      2
 2010-01-01 09:00:00      2010-01-01 09:03:35   5      3
 2010-01-01 11:09:36      2010-01-01 11:13:11   6      2
 2010-01-01 11:13:12      2010-01-01 11:16:47   6      4
*2010-01-01 14:30:00      2010-01-01 14:33:35   3      1
*2010-01-01 14:33:36      2010-01-01 14:37:11   3      2
*2010-01-01 14:37:12      2010-01-01 14:40:47   3      4

Output data (columns were there is no code would be null; above means use the start or date time as per the input rowset):

 Item  C1_Start  C1_End  C2_Start  C2_End  C3_Start  C3_End  C4_Start  C4_End
*3     above     above   null      null    above     above   above     above
 4     null      null    above     above   above     above   null      null
 5     null      null    above     above   above     above   null      null
 6     null      null    above     above   null      null    above     above
*3     above     above   above     above   null      null    above     above

Thanks

Are there only 4 Codes?

Felix Pamittan
– Felix Pamittan

2016-12-07 04:33:28 +00:00
Commented Dec 7, 2016 at 4:33 — Felix Pamittan
– Felix Pamittan, Commented Dec 7, 2016 at 4:33
No, Felix. The codes range from 0 to 9.

beliskna
– beliskna

2016-12-07 05:01:09 +00:00
Commented Dec 7, 2016 at 5:01 — beliskna
– beliskna, Commented Dec 7, 2016 at 5:01

Gordon Linoff · Accepted Answer · 2016-12-07 04:33:34Z

2

Just use conditional aggregation:

select item,
       max(case when code = 1 then startdate end) as c1_start,
       max(case when code = 1 then enddate end) as c1_end,
       max(case when code = 2 then startdate end) as c2_start,
       max(case when code = 2 then enddate end) as c2_end,
       max(case when code = 3 then startdate end) as c2_start,
       max(case when code = 3 then enddate end) as c3_end,
       max(case when code = 4 then startdate end) as c4_start,
       max(case when code = 4 then enddate end) as c4_end
from t
group by item;

answered Dec 7, 2016 at 4:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

beliskna Over a year ago

Thanks Gordon, I was working along a similar line but I couldn't get the last peice of the puzzle. I have ammened the question to illustrate my point.

Collectives™ on Stack Overflow

Convert SQL rows into columns based on separate blocks within the results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related