Post reply

Denormalize/Consolidate

  • July 29, 2009 at 10:46 am

    #93932

    I need to consolidate some data for in order to export for integration purposes. Please help if you can.

    CREATE TABLE TEST

    (

    ID int,

    Code int,

    CodeType char

    )

    INSERT INTO TEST

    SELECT 1, 10, 'A'

    UNION

    SELECT 1, 20, 'B'

    UNION

    SELECT 2, 15, 'A'

    UNION

    SELECT 2, 25, 'B'

    UNION

    SELECT 3, 35, 'A'

    UNION

    SELECT 3, 45, 'B'

    SELECT * FROM TEST

    DROP TABLE TEST

    I want results like

    ID,A,B

    1,10,20

    2,15,25,

    3,35,45

  • July 29, 2009 at 10:52 am

    #1031883

    declare @test-2 TABLE (

    ID int,

    Code int,

    CodeType char

    )

    INSERT INTO @test-2

    SELECT 1, 10, 'A'

    UNION

    SELECT 1, 20, 'B'

    UNION

    SELECT 2, 15, 'A'

    UNION

    SELECT 2, 25, 'B'

    UNION

    SELECT 3, 35, 'A'

    UNION

    SELECT 3, 45, 'B'

    SELECT * FROM @test-2

    SELECT ID,

    (SELECT Code FROM @test-2 WHERE ID = t1.ID AND CodeType = 'A') as 'A',

    (SELECT Code FROM @test-2 WHERE ID = t1.ID AND CodeType = 'B') as 'B'

    FROM @test-2 t1

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 29, 2009 at 1:08 pm

    #1031979

    Perfect, thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply