如何将前 4 行值放入每条visit_id记录的单独列中的一行中

Question

我有一个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 

Answer 1

使用 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

这将提供所需的输出，但我也建议谷歌搜索SQL动态透视以查看替代解决方案

Answer 2

您可以使用 XML 来实现此目的：

DECLARE @xml xml
;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))
, xml_t AS (
SELECT DISTINCT 1 as Tag,
        0 as Parent,
        pat_id as [pat!1!id], 
        visit_id  as [pat!1!visit_id],
        NULL as [visit!2!visit_code]
FROM patient_visit_codes 
UNION ALL
SELECT DISTINCT 2 as Tag,
        1 as Parent,
        pat_id as [pat!1!id], 
        visit_id as [pat!1!visit_id],
        visit_code as [visit!2!visit_code]
FROM patient_visit_codes)
SELECT @xml = (
SELECT *
FROM xml_t
ORDER BY [pat!1!id], [pat!1!visit_id],[visit!2!visit_code]      
FOR XML EXPLICIT) 
--we get xml like that:
--<pat id="1" visit_id="B58B3850-43BB-434D-B1EA-B968A01332EA">
--  <visit visit_code="1101F" />
--  <visit visit_code="3018F" />
--</pat>
--<pat id="56" visit_id="482A73F8-EA3F-4CD6-B1AD-BD2388EC5912">
--  <visit visit_code="I50" />
--  <visit visit_code="R50.90" />
--  <visit visit_code="Z02.89" />
--</pat>
SELECT  Component.value('@id','int') as pat_id,
        Component.value('@visit_id','nvarchar(32)') as visit_id,
        Component.value('(visit/@visit_code)[1]','nvarchar(10)') as visit_code1,
        Component.value('(visit/@visit_code)[2]','nvarchar(10)') as visit_code2,
        Component.value('(visit/@visit_code)[3]','nvarchar(10)') as visit_code3,
        Component.value('(visit/@visit_code)[4]','nvarchar(10)') as visit_code4
FROM @xml.nodes('/pat') as X(Component)

结果是：

pat_id      visit_id                         visit_code1 visit_code2 visit_code3 visit_code4
----------- -------------------------------- ----------- ----------- ----------- -----------
1           B58B3850-43BB-434D-B1EA-B968A013 1101F       3018F       NULL        NULL
56          482A73F8-EA3F-4CD6-B1AD-BD2388EC I50         R50.90      Z02.89      NULL
(2 row(s) affected)