时间戳

首页 > 解决方案 > SQL Server 2008 将数据从行重新排列到列

sql-server - SQL Server 2008 将数据从行重新排列到列

问题描述

我一直在为相当多的查询而苦苦挣扎，但没有任何成功。希望有人可以帮助我。我想使用 SQL Server 将数据从第一个表转换为第二个表格式。请参阅下表以了解我要完成的工作。我可以有多个发现实例。我会按等级转置每个发现。如您所见，实例是等级。

+----+---------+---------+-----------------+------------------+-----+
| 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 |
+----+-----------------+------------+------------------+------------+------------------+------------+-----------------+------------+-----------------+------------+------------------+------------+------------------+-----+

标签： sql-server

解决方案

正如已经建议的那样，条件聚合是解决方案的一种途径。我个人更喜欢这种方法，因为 PIVOT 的语法对我来说非常迟钝。与 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

推荐阅读