August 5, 2014 at 7:28 am
late edit.. title should say variable columns
Hi Folks
Thanks to the excellent Cross tabs article by Jeff Moden [/url] I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
)
SELECT ActivityRef,
max (case when rowno=1 then Initials else null end) as Col1,
max (case when rowno=2 then Initials else null end) as Col2,
max (case when rowno=3 then Initials else null end) as Col3,
max (case when rowno=4 then Initials else null end) as Col4,
max (case when rowno=5 then Initials else null end) as Col5,
max (case when rowno=6 then Initials else null end) as Col6
from ctePreAgg
group by ActivityRef
order by ActivityRef
brings back something like this..
ActivityRef,col1,col2,col3,col4,col5,col6
Ag-4xYSYSYSYSNULLNULLNULLNULL
Ag-6xYSYSBLBLNULLNULLNULLNULL
Ap-1xKWKWKWKWNULLNULLNULLNULL
At-2x SASSASCWCWNULLNULLNULLNULL
At-3x SASSASCWCWNULLNULLNULLNULL
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row..
this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
Make sense to anyone? Any help and guidance appreciated
Cheers
Les
August 5, 2014 at 8:24 am
Maybe you culd read Part 2 of the Cross Tabs and Pivots series. 😉
August 5, 2014 at 9:03 am
Luis Cazares (8/5/2014)
Maybe you culd read Part 2 of the Cross Tabs and Pivots series. 😉
I have read part two but couldn't see how it would fit this situation, but I can't claim to understand very much of it ..the further down the page I went the more lost I became :unsure:
August 26, 2014 at 5:49 am
Getting closer...
I'm able to output...
xg-14-A2-4xZS, ZS, ZS, ZS
xg-14-A2-6xZS, ZS, BP, BP
xp-14-A2-1bxLW, LW, LW, LW
xp-14-A2-1xLW, LW, LW, LW
xt-14-A2-2xSBS, SBS, CW, CW
xt-14-A2-3xSBS, SBS, CW, CW
but now I need to remove duplicates so it outputs
xg-14-a2-4x ZS
xg-14-a2-6x ZS, BP
;WITH
ctePreAgg AS
(
select top 500 t1.a_reference "ActivityRef",
row_number() over (partition by t1.a_reference order by t1.a_reference) as rowno,
t3.s_initials "Initials"
from mystuff), otherstuff as
(SELECT ActivityRef,
max (case when rowno=1 then Initials else null end) as Col1,
max (case when rowno=2 then Initials else null end) as Col2,
max (case when rowno=3 then Initials else null end) as Col3,
max (case when rowno=4 then Initials else null end) as Col4,
max (case when rowno=5 then Initials else null end) as Col5,
max (case when rowno=6 then Initials else null end) as Col6,
max (case when rowno=7 then Initials else null end) as Col7,
max (case when rowno=8 then Initials else null end) as Col8
from ctePreAgg
group by ActivityRef)
select ActivityRef, mergedstuff=stuff (coalesce(', '+ rtrim(col1), '')
+ coalesce(', ' + (rtrim(col2)), '')
+ coalesce(', ' + (rtrim(col3)), '')
+ coalesce(', ' + (rtrim(col4)), '')
+ coalesce(', ' + (rtrim(col5)), ''),1,2,'')
from otherstuff
group by ActivityRef, col1, col2, col3, col4, col5
order by ActivityRef
August 26, 2014 at 8:42 am
One option is to use group by inside your ctePreAgg, the other is to change ROW_NUMBER to DENSE_RANK.
September 24, 2014 at 7:23 am
That did it, thanks Luis
September 25, 2014 at 11:36 am
I don't think you need to go thru all that. Just modify the original query to get what you need. Not a lot of details, but something like this should do it:
;WITH
ctePreAgg AS
(
select top (500) act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
s_initials "Initials"
from (
select distinct act_reference, s_initials
from (
select top (500) act_reference, s_initials
from base_table
) as derived_1
where s_initials is not null
) as derived_2
order by act_reference
)
SELECT ActivityRef,
SUBSTRING(
max (case when rowno=1 then ',' + Initials else '' end) +
max (case when rowno=2 then ',' + Initials else '' end) +
max (case when rowno=3 then ',' + Initials else '' end) +
max (case when rowno=4 then ',' + Initials else '' end) +
max (case when rowno=5 then ',' + Initials else '' end) +
max (case when rowno=6 then ',' + Initials else '' end)
, 2, 200) AS initials
from ctePreAgg
group by ActivityRef
order by ActivityRef
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply