I have the below table
declare @t table
(id int identity, name varchar(50),sub1 int,sub2 int,sub3 int,sub4 int)
insert into @t
select 'name1',20,30,40,50 union all
select 'name2',10,30,40,50 union all
select 'name3',40,60,100,50 union all
select 'name4',80,30,40,80 union all
select 'name5',80,70,40,50 union all
select 'name6',10,30,40,80
The desired output should be
Id Name Sub1 Sub2 Sub3 Sub4
3 Name3 100
4 Name4 80 80
5 Name5 80 70
6 Name6 80
What I have done so far is
;with cteSub1 as
(
select rn1 = dense_rank() over(order by sub1 desc),t.id,t.name,t.sub1 from @t t
)
,cteSub2 as
(
select rn2 = dense_rank() over(order by sub2 desc),t.id,t.name,t.sub2 from @t t
)
,cteSub3 as
(
select rn3 = dense_rank() over(order by sub3 desc),t.id,t.name,t.sub3 from @t t
)
,cteSub4 as
(
select rn4 = dense_rank() over(order by sub4 desc),t.id,t.name,t.sub4 from @t t
)
select x1.id,x2.id,x3.id,x4.id ,x1.sub1,x2.sub2,x3.sub3,x4.sub4 from (select c1.id,c1.sub1 from cteSub1 c1 where rn1 =1) as x1
full join (select c2.id,c2.sub2 from cteSub2 c2 where rn2 =1)x2
on x1.id = x2.id
full join (select c3.id,c3.sub3 from cteSub3 c3 where rn3 =1)x3
on x1.id = x3.id
full join (select c4.id,c4.sub4 from cteSub4 c4 where rn4 =1)x4
on x1.id = x4.id
which is giving me the output as
id id id id sub1 sub2 sub3 sub4
5 5 NULL NULL 80 70 NULL NULL
4 NULL NULL 4 80 NULL NULL 80
NULL NULL 3 NULL NULL NULL 100 NULL
NULL NULL NULL 6 NULL NULL NULL 80
Help needed.
Also how can I reduce the number of CTE's?
