sql - 使用动态枢轴功能从水平到垂直显示

Question

我有一个一对多映射表，如下所示。我需要水平显示 ICD10。对于每个ICD9。数据是动态的，因此我无法使用静态枢轴函数。

ICD9 | ICD10
-----+------
0156 | 0178
0156 | 0179
0123 | 0181
0152 | 0202
0231 | 0210
0231 | 0211
0231 | 0212

我希望结果显示为-

ICD9 | ICD10 | ICD10 | ICD10
0156 | 0178  | 0179  | null
0123 | 0181  | null  | null
0152 | 0202  | null  | null
0231 | 0210  | 0211  | 0212

目前我尝试使用此代码:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(icd10) 
            FROM mv_icd
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT icd9, ' + @cols + ' from 
            (
                select icd9,icd10 from mv_icd
           ) x
            pivot 
            (
                min(icd9)
                for icd10 in (' + @cols + ')
            ) p '
            execute(@query)

但它不起作用，因为我有太多记录(大约12000)。如何更改代码以显示列中每个 ICD9 的 ICD10？

Answer 1

根据您当前的代码，您要将 ICD10 列中的所有 12000 个值转换为新列。这列太多了，对于任何用户来说都完全无法管理。

看来您确实希望将与 ICD9 关联的每个 ICD10 值转换为新列。为此，您需要使用诸如 row_number() 之类的窗口函数，并为每个 ICD10 创建一个唯一值，该值将用作新列名称。

您的查询将使用类似以下内容:

select icd9, icd10,
  rn = row_number() over(partition by icd9 order by icd10)
from mv_icd

参见Demo 。这给出了结果:

| ICD9 | ICD10 | RN |
|------|-------|----|
|  123 |   181 |  1 |
|  152 |   202 |  1 |
|  156 |   178 |  1 |
|  156 |   179 |  2 |
|  231 |   210 |  1 |
|  231 |   211 |  2 |
|  231 |   212 |  3 |

您现在有一个新列 rn，其中包含每个 ICD9 的 ICD10 值的数量。这个新列将在数据透视中用于创建新列。如果列数有限，则可以对查询进行硬编码:

select icd9, [1], [2], [3]
from 
(
  select icd9, icd10,
    rn = row_number() over(partition by icd9 order by icd10)
  from mv_icd
) d
pivot
(
  max(icd10)
  for rn in ([1], [2], [3])
) piv;

参见SQL Fiddle with Demo 。现在，如果您不知道每个 ICD9 总共有多少个 ICD10 项目，则必须使用动态 SQL。您可以将原始查询中的代码更改为:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

-- get list of the unique # of ICD10's per ICD9
SET @cols = STUFF((SELECT ',' + QUOTENAME(rn) 
            FROM
            (
              SELECT rn = row_number() over(partition by icd9 order by icd10)
              FROM mv_icd
            ) d
            GROUP BY rn
            ORDER BY rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
       = 'SELECT icd9, ' + @cols + ' 
          from 
          (
            select icd9, icd10,
              rn = row_number() over(partition by icd9 order by icd10)
            from mv_icd
           ) x
           pivot 
           (
             max(icd10)
             for rn in (' + @cols + ')
           ) p '

exec sp_executesql @query;

参见SQL Fiddle with Demo 。最终结果为:

| ICD9 |   1 |      2 |      3 |
|------|-----|--------|--------|
|  123 | 181 | (null) | (null) |
|  152 | 202 | (null) | (null) |
|  156 | 178 |    179 | (null) |
|  231 | 210 |    211 |    212 |

现在您可以将最终列的名称更改为您需要的任何名称，但这应该会为您提供所需的结果，而无需创建 12000 列。