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 TABLE (

    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

    SELECT ID,

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

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

    FROM @TEST 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 3 (of 3 total)

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