April 29, 2010 at 3:31 am
Experts..
i have name,value pairs of data in a table. ie data is in rows. can this be transposed to columns. each set will have 3 pairs of data (fixed).
if object_id('tempdb..#test1') is not null
drop table #test1
create table #test1 (id1 int,period1 int,pname1 varchar(10))
insert into #test1
select 1,1,'a'
union all
select 1,2,'b'
union all
select 1,3,'c'
union all
select 2,4,'d'
union all
select 2,5,'e'
union all
select 2,6,'f'
Expected output is:
id1 period1pname1period2pname2period3 pname3
1 1 a2b3c
24 d5e6f
TIA...
April 29, 2010 at 4:49 am
Beuatifully asked question and i-hope-it-is-beautiful code 😀
Here you go
;WITH perid_CTE (ID, c1, c2, c3) AS
(
SELECT ID1, [1] PERIOD1,[2] PERIOD2,[3] PERIOD3 FROM
(SELECT ID1, PERIOD1, ROW_NUMBER() OVER(PARTITION BY ID1 ORDER BY period1 ) R From #test1 ) PIVOT_TABLE
PIVOT
(MAX(PERIOD1) FOR R IN ([1],[2],[3])) PIVOT_HANDLE
)
, pname_CTE (ID, c1, c2, c3) AS
(
SELECT ID1, [1] pname1,[2] pname2,[3] pname3 FROM
(SELECT ID1, pname1, ROW_NUMBER() OVER(PARTITION BY ID1 ORDER BY pname1 ) R From #test1 ) PIVOT_TABLE
PIVOT
(MAX(pname1) FOR R IN ([1],[2],[3])) PIVOT_HANDLE
)
SELECT P.ID , P.C1 period1 , N.C1 pname1, P.C2 period2 , N.C2 pname2, P.C3 period3,N.C3 pname3
FROM
perid_CTE P
INNER JOIN
pname_CTE N
ON P.ID = N.ID
Tell us if thats waht you wanted!
Cheers!
April 29, 2010 at 5:17 am
April 29, 2010 at 5:21 am
Thanks, NewBeeSQL
April 29, 2010 at 5:53 am
Another variation of the same functionality but with much faster code
;WITH ORDERED_TABLE (ID, period1, R1, pname1, R2)
AS
(SELECT ID1
,period1, ROW_NUMBER() OVER(PARTITION BY ID1 ORDER BY period1) R1
,pname1 , ROW_NUMBER() OVER(PARTITION BY ID1 ORDER BY pname1 ) R2
FROM #test1
)
SELECT
ID,
MAX(CASE R1 WHEN 1 THEN period1 END) AS period1,
MAX(CASE R2 WHEN 1 THEN pname1 END) AS pname1,
MAX(CASE R1 WHEN 2 THEN period1 END) AS period2,
MAX(CASE R2 WHEN 2 THEN pname1 END) AS pname2,
MAX(CASE R1 WHEN 3 THEN period1 END) AS period3,
MAX(CASE R2 WHEN 3 THEN pname1 END) AS pname3
FROM
ORDERED_TABLE
GROUP BY
ID
Try this also and use the most appropriate one for your need 🙂
April 30, 2010 at 12:48 am
April 30, 2010 at 2:56 am
NewBeeSQL (4/30/2010)
Second one is so sweet and fast as you....thanks again......
Sheesh, NewBee..:blush: that one awesome appreciation.. i would say u are a Honey Bee.. lol...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply