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.
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..