sql-server - 如何将每个visit_id记录的前4行值放入单独的列中的一行中？

我有一个带有字段的patient_visit_codes表:pat_id, visit_id, visit_date, visit_code, create_timestamp。每个患者在每个访问日期可以有无限个访问代码。每个visit_code在单独的行中列出。我只想将前4个visit_code创建到单独列中的一行中。

因此，如果我的表包含以下数据:

;WITH patient_visit_codes AS (
SELECT * FROM (VALUES
(1, 'B58B3850-43BB-434D-B1EA-B968A01332EA', '2015-12-22', '3018F', '2015-12-22 07:24:07.403'),
(1, 'B58B3850-43BB-434D-B1EA-B968A01332EA', '2015-12-22', '1101F', '2015-12-22 07:28:09.440'),
(56, '482A73F8-EA3F-4CD6-B1AD-BD2388EC5912', '2016-01-05', 'I50', '2016-01-05 11:05:34.830'),
(56, '482A73F8-EA3F-4CD6-B1AD-BD2388EC5912', '2016-01-05', 'R50.90', '2016-01-05 11:35:14.560'),
(56, '482A73F8-EA3F-4CD6-B1AD-BD2388EC5912', '2016-01-05', 'Z02.89', '2016-01-05 11:40:24.740')
) as t (pat_id, visit_id, visit_date, visit_code, create_timestamp))

我希望获得如下结果:

pat_id  | visit_id                              | visit_code1   | visit_code2   | visit_code3   | visit_code4
1       | B58B3850-43BB-434D-B1EA-B968A01332EA  | 3018F         | 1101F         | Null          | Null
56      | 482A73F8-EA3F-4CD6-B1AD-BD2388EC5912  | I50           | R50.90        | Z02.89        | Null

最佳答案

使用MS SQL 2012，我想到了以下内容，可以为您提供所需的输出。它涉及创建一个子查询/CTE，以按日期对访问进行排名，然后进行4次自联接，以将所有访问都置于单个行条目中:

WITH cte
AS
    (SELECT pat_ID, visit_ID, visit_date, visit_code, create_timestamp,
        RANK() OVER(PARTITION BY pat_ID ORDER BY visit_date, create_timestamp) AS RankNo
    FROM #t)

SELECT a.pat_id, a.visit_id, a.visit_code AS visit_code1, b.visit_code AS visit_code2,
    c.visit_code AS visit_code3, d.visit_code AS visit_code4
FROM cte AS a
LEFT JOIN
    (SELECT * FROM cte AS b WHERE b.RankNo = 2) AS b
ON a.pat_ID = b.pat_ID
LEFT JOIN
    (SELECT * FROM cte AS c WHERE c.RankNo = 3) AS c
ON a.pat_id = c.pat_ID
LEFT JOIN
    (SELECT * FROM cte AS d WHERE d.RankNo = 4) AS d
ON a.pat_ID = d.pat_ID
WHERE
    a.RankNo = 1

对于RANK()函数，我对visit_date和create_timestamp都进行了ORDER BY处理以消除联系...如果有一个visit_date在创建的确切时间出现，您可能要添加第三个ORDER BY

这将提供所需的输出，但我也建议使用Googling SQL动态枢纽以查看替代解决方案

关于sql-server - 如何将每个visit_id记录的前4行值放入单独的列中的一行中？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34865421/