February 16, 2009 at 7:58 am
Simplified, but the source table is like:
[inc_id] player
--------------
1 Ned
1 Bob
1 Steve
2 Ned
2 Carl
3 Paul
Dest table SHOULD be:
[inc_id] player1 player2 player3
----------------------------
1 Ned Bob Steve
2 Ned Carl NULL
3 Paul NULL NULL
Can I take advantage of the PIVOT keyword to do this "flattening" of the source data? Am I missing something really obvious here? All my home grown solutions are taking too long.
I'll happily post more code, or clarify... should be near my machine all day.
Thanks SSC!
February 16, 2009 at 8:36 am
How about...create table #t ([inc_id] int, player varchar(10))
insert #t
select 1, 'Ned' union all
select 1, 'Bob' union all
select 1, 'Steve' union all
select 2, 'Ned' union all
select 2, 'Carl' union all
select 3, 'Paul'
select
[inc_id],
max(case rowid when 1 then player else null end) as 'player1',
max(case rowid when 2 then player else null end) as 'player2',
max(case rowid when 3 then player else null end) as 'player3'
from (
select
row_number() over(partition by [inc_id] order by [inc_id]) as rowid,
[inc_id],
player
from
#t
) x
group by
[inc_id]
drop table #t
Derek
February 16, 2009 at 9:04 am
Please try (utilizing pivot):DECLARE @test-2 TABLE (inc_id TINYINT
,player VARCHAR(20))
INSERT INTO @test-2 (inc_id,player)
SELECT 1,'Ned' UNION ALL
SELECT 1,'Bob' UNION ALL
SELECT 1,'Steve' UNION ALL
SELECT 2,'Ned' UNION ALL
SELECT 2,'Carl' UNION ALL
SELECT 3,'Paul';
WITH TestCTE AS(SELECT inc_id
, player
, ROW_NUMBER() OVER (PARTITION BY [inc_id]
ORDER BY [inc_id]) AS rowNumber
FROM @test-2)
SELECT *
FROM TestCTE
PIVOT
( MAX(player)
FOR [rowNumber] IN ([1],[2],[3]))
AS t
February 16, 2009 at 3:36 pm
This is perfect. Exactly what I was looking for.
SSC makes me look like a rockstar yet again. Something like 20:1 reduction versus the brute force method we had in place.
THANKS!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply