CODEWITHSUNDEEP
sql-server
user3263892
user3263892

Reputation: 93

SQL Server 2008 to rearrange data from rows to columns

I have struggled with a query for quite some without any success. Hopefully, someone can help me. I would like transpose data from the first table to second table format using SQL Server. Please see the below table to understand what I am trying to accomplish. I can have multiple instances of finding. I would transpose each finding by Rank. As you can see instances is ranks.

+----+---------+---------+-----------------+------------------+-----+
| ID | USER_ID | FINDING |   START DATE    |     END DATE     | RNK |
+----+---------+---------+-----------------+------------------+-----+
| 91 |      23 | AA      | 3/29/18 3:22 PM | 3/30/18 9:00 AM  |   1 |
| 42 |      23 | AA      | 3/29/18 3:22 PM | 3/30/18 12:55 PM |   2 |
| 41 |      23 | BB      | 3/29/18 3:22 PM | 3/30/18 10:10 AM |   1 |
| 45 |      23 | BB      | 3/29/18 3:22 PM | 3/30/18 1:50 PM  |   2 |
| 97 |      23 | CC      | 3/29/18 3:22 PM | 3/30/18 9:50 AM  |   1 |
| 43 |      23 | CC      | 3/29/18 3:22 PM | 3/30/18 1:15 PM  |   2 |
| 92 |      23 | DD      | 3/29/18 3:22 PM | 3/30/18 9:47 AM  |   1 |
| 42 |      23 | DD      | 3/29/18 3:22 PM | 3/30/18 1:05 PM  |   2 |
| 40 |      23 | EE      | 3/29/18 3:22 PM | 3/30/18 10:03 AM |   1 |
| 44 |      23 | EE      | 3/29/18 3:22 PM | 3/30/18 1:45 PM  |   2 |
| 46 |      23 | FF      | 3/29/18 3:22 PM | 3/30/18 10:00 AM |   1 |
| 48 |      23 | FF      | 3/29/18 3:22 PM | 3/30/18 1:30 PM  |   2 |
+----+---------+---------+-----------------+------------------+-----+


+----+-----------------+------------+------------------+------------+------------------+------------+-----------------+------------+-----------------+------------+------------------+------------+------------------+-----+
| ID |   START DATE    | FINDING AA |   END DATE AA    | FINDING BB |   END DATE BB    | FINDING CC |   END DATE CC   | FINDING DD |   END DATE DD   | FINDING EE |   END DATE EE    | FINDING FF |   END DATE FF    | RNK |
+----+-----------------+------------+------------------+------------+------------------+------------+-----------------+------------+-----------------+------------+------------------+------------+------------------+-----+
| 23 | 3/29/18 3:22 PM | AA         | 3/30/18 9:00 AM  | BB         | 3/30/18 10:10 AM | CC         | 3/30/18 9:50 AM | DD         | 3/30/18 9:47 AM | EE         | 3/30/18 10:03 AM | FF         | 3/30/18 10:00 AM |   1 |
| 23 | 3/29/18 3:22 PM | AA         | 3/30/18 12:55 PM | BB         | 3/30/18 1:50 PM  | CC         | 3/30/18 1:15 PM | DD         | 3/30/18 1:05 PM | EE         | 3/30/18 1:45 PM  | FF         | 3/30/18 1:30 PM  |   2 |
+----+-----------------+------------+------------------+------------+------------------+------------+-----------------+------------+-----------------+------------+------------------+------------+------------------+-----+

Upvotes: 1

Views: 28

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

As suggested already conditional aggregation is one avenue to a solution. I personally prefer this approach because the syntax for PIVOT is very obtuse for me. Also this has a very slight performance advantage over PIVOT.

declare @Something table
(
    ID int
    , USER_ID int
    , FINDING char(2)
    , STARTDATE datetime
    , ENDDATE datetime
    , RNK int
)

insert @Something values
(91, 23, 'AA', '3/29/18 3:22 PM', '3/30/18 9:00 AM', 1)
, (42, 23, 'AA', '3/29/18 3:22 PM', '3/30/18 12:55 PM', 2)
, (41, 23, 'BB', '3/29/18 3:22 PM', '3/30/18 10:10 AM', 1)
, (45, 23, 'BB', '3/29/18 3:22 PM', '3/30/18 1:50 PM', 2)
, (97, 23, 'CC', '3/29/18 3:22 PM', '3/30/18 9:50 AM', 1)
, (43, 23, 'CC', '3/29/18 3:22 PM', '3/30/18 1:15 PM', 2)
, (92, 23, 'DD', '3/29/18 3:22 PM', '3/30/18 9:47 AM', 1)
, (42, 23, 'DD', '3/29/18 3:22 PM', '3/30/18 1:05 PM', 2)
, (40, 23, 'EE', '3/29/18 3:22 PM', '3/30/18 10:03 AM', 1)
, (44, 23, 'EE', '3/29/18 3:22 PM', '3/30/18 1:45 PM', 2)
, (46, 23, 'FF', '3/29/18 3:22 PM', '3/30/18 10:00 AM', 1)
, (48, 23, 'FF', '3/29/18 3:22 PM', '3/30/18 1:30 PM', 2)

select s.USER_ID
    , StartDate = max(case when Finding = 'AA' then StartDate end)
    , FindingAA = max(case when Finding = 'AA' then Finding end)
    , EndDateAA = max(case when Finding = 'AA' then EndDate end)
    , FindingBB = max(case when Finding = 'BB' then Finding end)
    , EndDateBB = max(case when Finding = 'BB' then EndDate end)
    , FindingCC = max(case when Finding = 'CC' then Finding end)
    , EndDateCC = max(case when Finding = 'CC' then EndDate end)
    , FindingDD = max(case when Finding = 'DD' then Finding end)
    , EndDateDD = max(case when Finding = 'DD' then EndDate end)
    , FindingEE = max(case when Finding = 'EE' then Finding end)
    , EndDateEE = max(case when Finding = 'EE' then EndDate end)
    , FindingFF = max(case when Finding = 'FF' then Finding end)
    , EndDateFF = max(case when Finding = 'FF' then EndDate end)
    , RNK
from @Something s
group by s.USER_ID
    , s.RNK
order by s.RNK

Upvotes: 4

Related Questions