使用SQL SMS，如何将多行合并为一行，同时维护所有列数据

Question

我正在处理的数据目前的形式是:

 ID     Sex     Treatment    FillDate     
 1      M       ziprosidone  10/01/98     
 1      M       ziprosidone  10/15/98
 1      M       ziprosidone  10/29/98
 1      M       ambien       01/07/99
 1      M       ambien       01/14/99
 2      F       telaprevir   03/08/92
 2      F       telaprevir   03/20/92
 2      F       telaprevir   04/01/92

和我想写SQL代码来获取数据的形式:

 ID     Sex     DrugFill1     FillDate1_1     FillDate1_2     FillDate1_3    DrugFill2     FillDate2_1     FillDate2_2     FillDate2_3     
 1      M       ziprosidone   10/01/98        10/15/98        10/29/98       ambien        01/07/99        01/14/99        null
 2      F       telaprevir    03/08/92        03/20/92        04/01/92       null          null            null            null

我还想指出，我有超过10,000个id，每个id都有不同数量的药物和相应的填充日期。我想为那些没有数据要填写的列返回空值。

Answer 1

这个解决方案非常接近您所需要的。如果你需要进一步的帮助，把你的药并排放在一排，就告诉我。其实我不会这么做的……将其粘贴到空查询窗口中并执行。适应您的需求…

编辑:将此更改为您的新药物名称。

DECLARE @tbl TABLE(ID INT,Sex VARCHAR(1),Treatment VARCHAR(30),FillDate DATETIME);
INSERT INTO @tbl VALUES
 (1,'M','ziprosidone',{ts'1998-01-01 00:00:00'})     
,(1,'M','ziprosidone',{ts'1998-10-15 00:00:00'})
,(1,'M','ziprosidone',{ts'1998-10-29 00:00:00'})
,(1,'M','ambien',{ts'1999-01-07 00:00:00'})
,(1,'M','ambien',{ts'1999-01-14 00:00:00'})
,(2,'F','telaprevir',{ts'1992-03-08 00:00:00'})
,(2,'F','telaprevir',{ts'1992-03-02 00:00:00'})
,(2,'F','telaprevir',{ts'1992-04-01 00:00:00'});
WITH DistinctIDs AS
(
    SELECT DISTINCT ID,Sex FROM @tbl
)
,DistinctDrugs AS
(
    SELECT DISTINCT DistinctIDs.ID
                   ,DistinctIDs.Sex
                   ,tbl.Treatment
                   ,'Drug_' + CAST(ROW_NUMBER() OVER(PARTITION BY tbl.ID,tbl.Treatment ORDER BY tbl.Treatment) AS VARCHAR(100)) AS PivotColumnName 
    FROM DistinctIDs
        INNER JOIN @tbl AS tbl ON DistinctIDs.ID=tbl.ID
)
SELECT p.ID 
      ,p.Sex
      ,p.Treatment
      ,CAST(p.Drug_1 AS XML).value('/root[1]/item[1]/FillDate[1]','datetime') AS Date_1
      ,CAST(p.Drug_1 AS XML).value('/root[1]/item[2]/FillDate[1]','datetime') AS Date_2
      ,CAST(p.Drug_1 AS XML).value('/root[1]/item[3]/FillDate[1]','datetime') AS Date_3
FROM
(
    SELECT DistinctDrugs.*
          ,SubRows.XMLContent
    FROM DistinctDrugs
    CROSS APPLY
    (
        SELECT tbl.FillDate
        FROM @tbl  AS tbl
        WHERE tbl.ID=DistinctDrugs.ID AND tbl.Treatment=DistinctDrugs.Treatment
        FOR XML PATH('item'),ROOT('root')
    ) AS SubRows(XMLContent)
) AS DataToPivot
PIVOT
(
    MIN(XMLContent) FOR PivotColumnName IN(Drug_1,Drug_2,Drug_3 /*as many as needed*/)
) AS p