Post reply

PIVOT maybe?

  • February 22, 2012 at 12:02 pm

    #391623

    I am trying to create this query and I think I need to do it using PIVOT. I am not sure of the syntax.

    The current table:

    Id Field1 Field2 Field3

    1 NA MARY NA

    2 NA NA NA

    2 JOE NA NA

    3 NA NA MIKE

    I need my query to return this.

    Id Field Value

    1 Field1 NA

    1 Field2 MARY

    1 Field3 NA

    2 Field1 NA

    2 Field2 NA

    2 Field3 NA

    2 Field1 JOE

    2 Field2 NA

    2 Field3 NA

    3 Field1 NA

    3 Field2 NA

    3 Field3 MIKE

    Thanks

    Tim

  • February 22, 2012 at 12:09 pm

    #1450509

    Dont understand what you are trying to do.. could be more specific on what you really want?

    Please take a gander at this link on how to help us help you : http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • February 22, 2012 at 12:22 pm

    #1450514

    This, my friend?

    DECLARE @tab TABLE

    ( Id INT ,

    Field1 VARCHAR(15),

    Field2 VARCHAR(15),

    Field3 VARCHAR(15)

    )

    INSERT INTO @tab

    SELECT 1 ,'NA','MARY' ,'NA'

    UNION ALL SELECT 2 ,'NA','NA' ,'NA'

    UNION ALL SELECT 2 ,'JOE' ,'NA' ,'NA'

    UNION ALL SELECT 3 ,'NA' ,'NA' ,'MIKE'

    SELECT Unpivot_Handle.Id, Unpivot_Handle.Cols, Unpivot_Handle.ColVal

    FROM @tab Unpivot_Source

    UNPIVOT

    ( ColVal FOR Cols IN (Field1,Field2,Field3)) Unpivot_Handle

    You need UNPIVOT!

  • February 22, 2012 at 12:24 pm

    #1450516

    is this an example of what you are trying to achieve?

    DECLARE @sample TABLE (

    id INT IDENTITY(1, 1),

    product VARCHAR(10),

    location VARCHAR(10),

    qoh VARCHAR(10))

    INSERT INTO @sample

    SELECT 'proda','12','24' UNION ALL

    SELECT 'prodb','12','36'

    SELECT * FROM @sample

    SELECT product, ca.*

    FROM @sample

    CROSS APPLY

    ( VALUES ('Location', location),

    ('QOH', qoh) ) ca ([column], [row])

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • February 22, 2012 at 12:37 pm

    #1450519

    I am getting error when I run this.

  • February 22, 2012 at 12:40 pm

    #1450520

    TT-131210 (2/22/2012)

    I am getting error when I run this.

    Mmm hmm... What version of SQL Server are you running?

    HINT: Run this SELECT @@version

    I believe ColdCoffee's suggestion is exactly what you need.

    Jared
    CE - Microsoft

  • February 22, 2012 at 1:15 pm

    #1450532

    SQLKnowItAll (2/22/2012)

    TT-131210 (2/22/2012)

    I am getting error when I run this.

    Mmm hmm... What version of SQL Server are you running?

    HINT: Run this SELECT @@version

    I believe ColdCoffee's suggestion is exactly what you need.

    This should illustrate the point a bit better:

    DECLARE @tab TABLE

    ( Id INT ,

    Field1 VARCHAR(15),

    Field2 VARCHAR(15),

    Field3 VARCHAR(15)

    )

    INSERT INTO @tab

    SELECT 1, 'NA', 'NAM' , 'NAMD'

    UNION ALL

    SELECT 2, 'BA', 'BNA', 'BNAM'

    UNION ALL

    SELECT 3, 'HA', 'HNA', 'HNAD'

    UNION ALL

    SELECT 4, 'LA', 'LNA', 'LNAD'

    SELECT Unpivot_Handle.Id, Unpivot_Handle.Cols, Unpivot_Handle.ColVal

    FROM @tab Unpivot_Source

    UNPIVOT

    (ColVal FOR Cols IN (Field1, Field2, Field3)) Unpivot_Handle

    Jared
    CE - Microsoft

  • February 22, 2012 at 1:20 pm

    #1450537

    They both worked. I am developing on SQL 2005 and it does work in 2008.

    Thank you

    Tim

Viewing 8 posts - 1 through 7 (of 7 total)

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