How to Pivot on two columns in SQL Server

Question

Below is the data rows I have:

ID     Code      OtherCol
7      Code1      NULL
7      code2      NULL
2      unk        NULL
4      unk        NULL
3      Code2      NULL
3      Code3      NULL
3      Code5      Other1
5      Code4      NULL
5      Code5      Other2

I am trying get this displayed as

ID name1 name2 name3 name4 name5 nameunk Othername
2                                unk
3        code2 code3       code5         Other1
4                                unk                    
5                    code4 code5         Other2
7  code1 code2

I was able to pivot the first column but having a problem pivoting the second one.

And also there is a name for a given code, but the value under OtherCol are random.

Welcome to SO, but your question is not clear, what is `name1` to `name5`? I suppose `code1` is shown under `name1` so it is corresponding to code but then why is `code4` shown under `name5`? How many unique values you want to pivot? what happen if `unk` value occur twice for given ID? — abdul qayyum, Dec 13 '18 at 11:07
Thank for pointing that out, it was mistake. and 'unk' will only happen once for a given ID — Kumar2127, Dec 13 '18 at 11:17
just give some contest an ID can have any code from Code1 through Code5 including 'unk' (unknown) which corresponds to given name, only twist is sometime ID can have totaly random value (code5 which identifies 'other value' and additional column 'OtherCol' to capture the corresponding value) along with any of the other codes, hope that helps — Kumar2127, Dec 13 '18 at 11:30

score 2 · Answer 1 · answered Dec 13 '18 at 13:12

I recommend conditional aggregation:

select id,
       max(case when code = 'code1' then code end) as name1,
       max(case when code = 'code2' then code end) as name2,
       max(case when code = 'code3' then code end) as name3,
       max(case when code = 'code4' then code end) as name4,
       max(case when code = 'code5' then code end) as name5,
       max(case when code = 'unk' then code end) as nameunk,
       max(othercol) as othercol
from t
group by id;

gotqn · Answer 2 · 2018-12-13T11:51:23.713

This is full working example. You can change it a little bit to match your real data.

CREATE TABLE #DataSource
(
    [ID] INT
   ,[Code] VARCHAR(12)
   ,[OtherCol] VARCHAR(12)
);

INSERT INTO #DataSource ([ID], [Code], [OtherCol])
VALUES (7, 'Code1', NULL)
      ,(7, 'code2', NULL)
      ,(2, 'Unk', NULL)
      ,(4, 'Unk', NULL)
      ,(3, 'Code2', NULL)
      ,(3, 'Code3', NULL)
      ,(3, 'Code5', 'Other1')
      ,(5, 'Code4', NULL)
      ,(5, 'Code4', 'Other2');


DECLARE @DynammicTSQLStatement NVARCHAR(MAX)
       ,@DynamicPIVOTColumns NVARCHAR(MAX);


SET @DynamicPIVOTColumns = STUFF
                          (
                                (
                                SELECT ',[' + CAST([value] AS VARCHAR(12)) + ']'
                                FROM 
                                (
                                    SELECT 0
                                         ,DENSE_RANK() OVER (ORDER BY [Code])
                                         ,REPLACE([Code], 'Code', 'name')
                                    FROM #DataSource
                                    WHERE [Code] IS NOT NULL
                                    UNION
                                    SELECT 1
                                          ,1
                                          ,'OtherCol'
                                ) DS ([GroupID],[RowID], [value])
                                ORDER BY [GroupID], [RowID]
                                FOR XML PATH('') ,TYPE
                                ).value('.', 'NVARCHAR(MAX)')
                                ,1
                                ,1
                                ,''
                          );

SET @DynammicTSQLStatement = N'
SELECT *
FROM
(
    SELECT [ID]
          ,[Code]
          ,REPLACE([Code], ''Code'', ''name'')
    FROM #DataSource
    UNION ALL
    SELECT [ID]
          ,[OtherCol]
          ,''OtherCol''
    FROM #DataSource
) DS ([ID], [value], [column])
PIVOT
(
    MAX([value]) FOR [column] IN (' + @DynamicPIVOTColumns + ')
) PVT';

EXEC sp_executesql @DynammicTSQLStatement;

DROP TABLE #DataSource;

Thanks buddy appreciate the help, trying to see how i can avoid dynamic SQL since i have to call this final table into another view — Kumar2127, Dec 13 '18 at 11:58
@Kumar2127 If you are columns are static :D just add `SELECT @DynammicTSQLStatement` and use the statement in your view :-) ok? — gotqn, Dec 13 '18 at 11:59

abdul qayyum · Answer 3 · 2018-12-13T12:04:31.577

--PIVOT THE TABLE
select ID,[code1],[code2], [code3],[code4],[code5],[Unk]
into #resPivot
from 
(
  select ID, code
  from tblTest
) src
pivot
(
  max(code)
  for code in ([code1], [code2], [code3],[code4],[code5],[Unk])
) piv;

--FIND ALL COLS WHERE OTHER COLUMN have value row 3,5 in your example
SELECT * INTO #distinct FROM tblTest where tblTest.otherCol IS NOT NULL

--PIVOTED RESULT WITH ABOVE TABLE
select distinct #resPivot.ID,[code1], [code2], [code3],[code4],[code5],[Unk],#distinct.otherCol
into #otherCol
from #resPivot  inner join #distinct
on #distinct.id = #resPivot.id 

--THIS IS PIVOTED RESULT WITH ALL RESULTS THAT HAS NO OTHER COL VALUE UNION with OTHER CALL VALUE 
select distinct #resPivot.ID,[code1], [code2], [code3],[code4],[code5],[Unk],tblTest.otherCol 
from #resPivot  inner join tblTest
on tblTest.id = #resPivot.id 
WHERE otherCol IS NULL and tblTest.ID NOT IN (SELECT ID FROM #otherCol)
UNION ALL
Select * from #otherCol

--DROP TEMP TABLES
Drop Table #resPivot
Drop Table #distinct
Drop Table #otherCol

A little simpler and faster version

How to Pivot on two columns in SQL Server

3 Answers3