Horizontal to vertical display using Dynamic Pivot Function

Question

I have a one to many mapping table displayed as below. I need to display the ICD10 HORIZONTALLY. for each ICD9. The data is dynamic hence I cannot use the static pivot function.

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

I want the result to be displayed as-

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

Currently I tried to use this code:

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)

but it does not work as I have too many records (about 12000). How can I alter the code to display the ICD10 for each ICD9 in a column?

Answer 1

Based on your current code, you are converting all 12000 values in the ICD10 column into new columns. That is far too many columns and completely unmanageable for any user.

It appears that you really want to convert each ICD10 value associated with ICD9 into new columns. To do this, you'll need to use a windowing function like row_number() and create a unique value for each ICD10 that will be used as the new column names.

Your query will using something like:

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

See Demo. This gives a result:

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

You now have a new column rn that contains the number of ICD10 values for each ICD9. This new column will be used in the pivot to create your new columns. If you have a limited number of columns, then you can hard code the query:

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;

See SQL Fiddle with Demo. Now if you don't know how many total ICD10 items you'd have for each ICD9 you'll have to use dynamic SQL. You'd alter your code in your original query to be:

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;

See SQL Fiddle with Demo. These give a final result of:

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

Now you can alter the names of the final columns to whatever you need, but this should give you the result that you want without creating 12000 columns.