up to 3 rows in source table... needs flattening into 1 row in dest.

  • 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!

  • 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

  • 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

  • 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