CODEWITHSUNDEEP
sqlsql-serversql-server-2008
MoonKnight
MoonKnight

Reputation: 23831

Dynamic Insert with Pivot

All I have the following two table definitions 

CREATE TABLE ICD ([EpiNum] varchar(4), [ICDCode] varchar(4));
GO
INSERT INTO ICD ([EpiNum], [ICDCode])
VALUES
    ('1', 'A1'),
    ('1', 'A2'),
    ('1', 'A3'),
    ('2', 'B1'),
    ('2', 'B2'),
    ('3', 'C3');
GO

CREATE TABLE TempEpisode ([EpiNum] varchar(4), [X1] varchar(4), [X2] varchar(4), [X3] varchar(4));
GO
INSERT INTO TempEpisode ([EpiNum], [X1], [X2], [X3])
VALUES
    ('1', '', '', ''),
    ('2', '', '', ''),
    ('3', '', '', '');
GO

I want to know how I can update TempEpisode so that I get 

    ('1', 'A1', 'A2', 'A3'),
    ('2', 'B1', 'B2', ''),
    ('3', 'C3', '', '');

as the output? Note, there a 3 codes for EpiNum 1, 2 codes for EpiNum 2, 1 for EpiNum 3. So I want to group these duplicates column-wise.

What I have done... I initially set out taking a pivot table approach; this has got complex and I am not entirely sure how I can get what I require from it. The existing code I have is 

DECLARE @cols AS NVARCHAR(MAX), @columns AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT  ',' + QUOTENAME(ICDCode) 
                    FROM ICD
                    GROUP BY ICDCode
                    ORDER BY ICDCode
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');
PRINT @cols;

SELECT @columns = 
    STUFF((SELECT ', coalesce(' + 
              QUOTENAME(ICDCode)+', '''') as ' + 
              QUOTENAME('X' + ICDCode)
           FROM ICD
           GROUP BY ICDCode
           ORDER BY ICDCode
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = 'SELECT EpiNum, ' + @columns + ' 
              FROM 
              (
                 SELECT EpiNum, ICDCode, ICDCode AS flag
                 FROM ICD
              ) AS x
              PIVOT 
              (
                 MAX(flag)
                 FOR ICDCode IN (' + @cols + ')
              ) AS p ';
execute(@query);

This produces:

EpiNum  XA1  XA2  XA3  XB1  XB2  XC3
1       A1   A2   A3
2                      B1   B2  
3                                C3

I want

EpiNum  XA1  XA2  XA3
1       A1   A2   A3
2       B1   B2  
3       C3

Upvotes: 0

Views: 50

Answers (1)

podiluska
podiluska

Reputation: 51504

Try this

insert TempEpisode
select * 
from
( 
    select *, ROW_NUMBER() over (partition by epinum order by icdcode) rn
    from ICD
) p
pivot (max(icdcode) for rn in ([1],[2],[3])) p2

Things that you need to consider.

  1. How do you define the order of episodes - is there an extra field that isn't shown?
  2. Is this a good idea - why are you de-normalising your data?

Upvotes: 1

Related Questions