SQL Server Pivot using multiple sets of columns

Question

From a table like this:

CREATE TABLE dbo.mytable 
(
     [ID] int, 
     [Category] INT, 
     [Lh] varchar(30), 
     [Sev] INT, 
     [Risk] INT
)

insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (5, 2, 'Impossible', 4, 10)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 2, 'Unlikely', 3, 13)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 3, 'Possible', 3, 18)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 5, 'Likely', 3, 23)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 6, 'Possible', 3, 18)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (7, 2, 'Impossible', 5, 15)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (8, 2, 'Very Unlikely', 5, 20)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (9, 2, 'Unlikely', 6, 30)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (10, 2, 'Impossible', 3, 6)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (10, 6, 'Impossible', 3, 6)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (12, 1, 'Impossible', 4, 10)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (12, 2, 'Very Unlikely', 5, 20)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (12, 4, 'Impossible', 3, 6)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (13, 2, 'Impossible', 6, 21)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (14, 2, 'Impossible', 6, 21)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (15, 1, 'Very Unlikely', 3, 6)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (15, 2, 'Impossible', 5, 15)

I am trying to create a result set that looks like this (bearing in mind that there are any number of Categories possible, but always 3 components to every category):

ID  Cat_1_Lh    Cat_1_Sev   Cat_1_Risk  Cat_2_Lh    Cat_2_Sev   Cat_2_Risk  Cat_3_Lh    Cat_3_Sev   Cat_3_Risk  Cat_4_Lh    Cat_4_Sev   Cat_4_Risk  Cat_5_Lh    Cat_5_Sev   Cat_5_Risk  Cat_6_Lh    Cat_6_Sev   Cat_6_Risk
5                                       Impossible      4           10                                              
6                                       Unlikely        3           13      Possible        3           18                                          Likely          3           23      Possible        3           18
7                                       Impossible      5           15                                              
8                                       Very Unlikely   5           20                                              
9                                       Unlikely        6           30                                              
10                                      Impossible      3           6                                                                                                                   Impossible      3            6
12  Impossible      4           10      Very Unlikely   5           20                                          Impossible      3           6                       
13                                      Impossible      6           21                                              
14                                      Impossible      6           21                                              
15  Very Unlikely   3           6       Impossible      5           15

I have looked at and tried to modify a range of pivot and unpivot solutions presented here, with dynamic and static column definitions but none of them look like they have the remotest chance of working so I don't know which ones to suggest can be leveraged into a solution.

I would very appreciate some guidance as to which process/mechanism would be best suited to this.

Thanks in advance.

Upvote for sample data,posting expected results in format as text helps people who are in domains where images are blocked — TheGameiswar, Sep 20 '16 at 15:52

John Cappelletti · Answer 1 · 2016-09-20T16:47:11.213

If you don't mind going dynamic

Declare @SQL varchar(max) = ''
Declare @BaseCols varchar(max) ='
,Cat_#_Lh   = max(case when Category=# then LH   else '''' end)
,Cat_#_Sev  = max(case when Category=# then cast(Sev  as varchar(25)) else '''' end)
,Cat_#_Risk = max(case when Category=# then cast(Risk as varchar(25)) else '''' end)
'
Select @SQL = @SQL + Replace(@BaseCols,'#',Category) from (Select Distinct Category from myTable) A 
Set @SQL = 'Select ID'+@SQL + ' From myTable Group By ID Order by ID'
Exec(@SQL)

Returns

score 0 · Accepted Answer · answered Sep 20 '16 at 16:31

One naive way to solve this problem is to write query like this

;with cte_lh as (
select Id, max([1]) as Cat_1_lh,max([2]) as Cat_2_lh,max([3]) as Cat_3_lh,max([4]) as Cat_4_lh,max([5]) as Cat_5_lh,max([6]) as Cat_6_lh from mytable 
pivot(max(Lh) for Category in ([1],[2],[3],[4],[5],[6])) as p
group by id
), cte_sev as (
select id,  max([1]) as Cat_1_Sev,max([2]) Cat_2_Sev,max([3]) Cat_3_Sev,max([4]) Cat_4_Sev,max([5]) Cat_5_Sev,max([6]) Cat_6_Sev from mytable
pivot (max(sev) for category in ([1],[2],[3],[4],[5],[6])) as p
group by id
), cte_risk as (
select id,  max([1]) Cat_1_Risk,max([2]) Cat_2_Risk,max([3]) Cat_3_Risk,max([4]) Cat_4_Risk,max([5]) Cat_5_Risk,max([6]) Cat_6_Risk from mytable
pivot (max(risk) for category in ([1],[2],[3],[4],[5],[6])) as p
group by id
) select * from cte_lh lh join cte_sev sev on lh.id = sev.id
join cte_risk risk on lh.id = risk.id

We can create columns in dynamic sql and add columns and create dynamic sql if your column list is varying..

I went with this approach as: a) it was understandable and easy to include back into the more complicated scenario; b) it enabled the entire query to be accomplished in one pass; c) it performs very well on a large DB; d) it can be incorporated into a view and therefore made more useful. Thanks very much for your help. — Alex, Sep 21 '16 at 07:14
Thanks and welcome, yeah i did an execution plan comparison and found this approach is better. Also I found John's approach is very interesting.. so only I upvoted his approach... — Kannan Kandasamy, Sep 21 '16 at 15:06

score 0 · Answer 3 · answered Sep 20 '16 at 16:44

This will need to be done with a dynamic query as pivoting, even with tricks, still does not support a dynamic number of columns. Here is an example of the dynamic SQL to create the table. I leave populating it to you.

DECLARE @stmt nvarchar(max) = '
DECLARE @tbl TABLE
(
    '

DECLARE @mytable TABLE
(
     [ID] int, 
     [Category] INT, 
     [Lh] varchar(30), 
     [Sev] INT, 
     [Risk] INT
)

insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (5, 2, 'Impossible', 4, 10)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 2, 'Unlikely', 3, 13)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 3, 'Possible', 3, 18)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 5, 'Likely', 3, 23)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 6, 'Possible', 3, 18)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (7, 2, 'Impossible', 5, 15)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (8, 2, 'Very Unlikely', 5, 20)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (9, 2, 'Unlikely', 6, 30)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (10, 2, 'Impossible', 3, 6)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (10, 6, 'Impossible', 3, 6)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (12, 1, 'Impossible', 4, 10)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (12, 2, 'Very Unlikely', 5, 20)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (12, 4, 'Impossible', 3, 6)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (13, 2, 'Impossible', 6, 21)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (14, 2, 'Impossible', 6, 21)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (15, 1, 'Very Unlikely', 3, 6)
insert into @mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (15, 2, 'Impossible', 5, 15)

DECLARE @categories TABLE
(
    [Category] INT
)

INSERT @categories SELECT DISTINCT [Category] FROM @mytable ORDER BY [Category]

WHILE ((SELECT TOP 1 [Category] FROM @categories ORDER BY [Category]) IS NOT NULL)
BEGIN
    DECLARE @category int = (SELECT TOP 1 [Category] FROM @categories ORDER BY [Category])
    SET @stmt = @stmt + '[Cat_' + CAST(@category AS VARCHAR(max)) + '_Lh] VARCHAR(MAX)
    ,[Cat_' + CAST(@category AS VARCHAR(max)) + '_Sev] INT
    ,[Cat_' + CAST(@category AS VARCHAR(max)) + '_Risk] INT
' + CASE
        WHEN (SELECT count(*) FROM @categories) > 1 THEN '  ,'
        ELSE ')'
        END
DELETE TOP (1) FROM @categories
END

SET @stmt = @stmt + '

SELECT * FROM @tbl [tbl]'

EXEC sp_executesql @stmt

SQL Server Pivot using multiple sets of columns

3 Answers3