Vertical horizontal display using the dynamic rotation function

Question

I have a mapping table from one to many, as shown below. I need to display ICD10 HORIZONTAL. for each ICD9 . The data is dynamic, so I cannot use the static reference function.

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

I want the result to display as -

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

I have currently tried using 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 because I have too many records (about 12000). How can I change the code to display ICD10 for each ICD9 in a column?

Answer 1

Based on your current code, you will convert all 12000 values ​​in the ICD10 column to new columns. This is too many columns and is completely out of control for any user.

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

Your request will use something like:

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

See Demo . This gives the 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 rn column that contains the number of ICD9 values ​​for each ICD9 . This new column will be used in the vault to create new columns. If you have a limited number of columns, you can program 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 do not know how many total ICD9 elements you will have for each ICD9 , you will have to use dynamic SQL. You would change your code in the original request:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) -- get list of the unique # of ICD10 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 . This gives the final result:

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

Now you can change the names of the final columns to whatever you need, but this should give you the result you want without creating 12,000 columns.