converting column into rows

  • Hi ,

    Can some one help me on generating Tsql for converting Colums in to rows.

    Example as below

    Table 1

    Value

    V1

    V2

    V3

    V4

    V5

    Table 2

    ID Value

    ID1 V1

    ID1 V2

    ID1 V3

    ID2 V2

    ID2 V5

    Desired Output

    ID V1 V2 V3 V4 V5

    ID1 YES YES YES NO NO

    ID2 NO YES NO NO YES

  • is your maximum column V5 or V9999



    Clear Sky SQL
    My Blog[/url]

  • Dave Ballantyne (7/10/2009)


    is your maximum column V5 or V9999

    Hi dave,

    Maximum colum is random and can be up to 100. It depends on the numbers of items being added or deleted.

    Thanks

    Vips

  • hi ,

    no solution is particularly clean but this should do you

    select table2.id,

    coalesce(max(Case when table2.Value = 'V1' then 'Yes' end),'No'),

    coalesce(max(Case when table2.Value = 'V2' then 'Yes' end),'No'),

    coalesce(max(Case when table2.Value = 'V3' then 'Yes' end),'No'),

    coalesce(max(Case when table2.Value = 'V4' then 'Yes' end),'No'),

    coalesce(max(Case when table2.Value = 'V5' then 'Yes' end),'No') --etc

    from table2

    group by table2.id



    Clear Sky SQL
    My Blog[/url]

  • Something like this should work. Only drawback is that you have to know what the different possible values are beforehand. If you don't know that, then it's still doable, but you'll need to use a dynamic SQL query to accomplish it. I have a script for that as well, so if you need that I can probably figure something out for you.

    DECLARE @Table2 TABLE

    (

    ID INT,

    [Value] VARCHAR(20)

    )

    INSERT INTO @Table2

    SELECT 1, 'V1'

    UNION

    SELECT 1, 'V2'

    UNION

    SELECT 1, 'V3'

    UNION

    SELECT 2, 'V2'

    UNION

    SELECT 2, 'V5'

    SELECT

    ID,

    [V1] AS [V1],

    [V2] AS [V2],

    [V3] AS [V3],

    [V4] AS [V4],

    [V5] AS [V5]

    FROM

    (

    SELECT

    ID,

    [Value]

    FROM @Table2

    ) RS

    PIVOT

    (

    COUNT([Value])

    FOR

    [Value] IN ([V1],[V2],[V3],[V4],[V5])

    ) AS pvt

    On an unrelated note - how exactly do you post code in here that looks formatted properly? I mean the script looks fine in my SQL Server Management Studio, but here it looks terrible.

  • Read this to solve the problem of an unknown number of columns.

    http://www.sqlservercentral.com/articles/cross+tab/65048/

    What I'd like to know is why do you need to denormalize data in such a fashion? Spreadsheet?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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