Search code examples
t-sqlrowtranspose

Use T-SQL to transpose two text columns

Can I use a T-SQL query to transpose rows of text data from this:

enter image description here

to columns like this:

enter image description here

where the number of trainings a person can attend varies?

Solution

  • Hopefully this helps. If any ID has more than 3 training courses, the excess training courses will not appear.

    SELECT OrgDefinedID
    , max(CASE WHEN row_num = 1 THEN attended END) Attendance1
    , max(CASE WHEN row_num = 1 THEN training END) Training1
    , max(CASE WHEN row_num = 2 THEN attended END) Attendance2
    , max(CASE WHEN row_num = 2 THEN training END) Training2
    , max(CASE WHEN row_num = 3 THEN attended END) Attendance3
    , max(CASE WHEN row_num = 3 THEN training END) Training3

FROM
(SELECT row_number() over (partition by OrgDefinedID order by Attended, Training) as row_num, *  
FROM T1) x 
group by OrgDefinedID

    What is the max() doing? My first attempt was this

    SELECT OrgDefinedID
    , (CASE WHEN row_num = 1 THEN attended END) Attendance1
    , (CASE WHEN row_num = 1 THEN training END) Training1
    , (CASE WHEN row_num = 2 THEN attended END) Attendance2
    , (CASE WHEN row_num = 2 THEN training END) Training2
    , (CASE WHEN row_num = 3 THEN attended END) Attendance3
    , (CASE WHEN row_num = 3 THEN training END) Training3

FROM
(SELECT row_number() over (partition by OrgDefinedID order by Attended, Training) as row_num, *  
FROM T1) x

    which is almost there. We just need to collapse multiple rows into one row

    OrgDefinedID Attendance1 Training1 Attendance2 Training2 Attendance3 Training3
    13076 Yes CPIE Online Training Studio null null null null
    13076 null null Yes AIDI null null
    12505478 Yes Best Practices 3, 9AM null null null null
    12505478 null null Yes Work Smarter II, #4 null null
    12505478 null null null null Yes Rubric Design 2

    You can see that within a given OrgDefinedID, each column has a single non-null value and the other values in that column are null. So if we group by OrgDefinedID and apply max(), it will pick the non-null value we want. Note that aggregate functions like max() ignore nulls. So adding the max() function and grouping by OrgDefinedID, we get:

    OrgDefinedID Attendance1 Training1 Attendance2 Training2 Attendance3 Training3
    13076 Yes CPIE Online Training Studio Yes AIDI null null
    12505478 Yes Best Practices 3, 9AM Yes Work Smarter II, #4 Yes Rubric Design 2