Post reply

Query Help on PIVOT

  • April 10, 2018 at 4:14 pm

    #410396

    Hello, Please help me with below query, 
    need a row for each cell item, like cartestian product.
    Thanks for your help, Thanks for your time, Very much appreciated of your support.
     
     Create Table #t (
        Id varchar(5)
        , C1 varchar(5)
        , C2 varchar(5)
        , C3 varchar(5)
        , C4 varchar(5)
        )
        select * from #t
        Insert into #t values ('R1', 'R1C1','R1C2','R1C3','R1C4')
        Insert into #t values ('R2', 'R2C1','R2C2','R2C3','R2C4')

        Please help with Query to get result as below, ID    col1    Col2
    R1    C1    R1C1
    R1    C2    R1C2
    R1    C3    R1C3
    R1    C4    R1C4
    R2    C1    R2C1
    R2    C2    R2C2
    R2    C3    R2C3
    R2    C4    R2C4

  • April 10, 2018 at 6:36 pm

    #1986246

    Create Table #t (
    Id varchar(5)
    , C1 varchar(5)
    , C2 varchar(5)
    , C3 varchar(5)
    , C4 varchar(5)
    );
    INSERT #t VALUES
        ('R1', 'R1C1','R1C2','R1C3','R1C4')
      , ('R2', 'R2C1','R2C2','R2C3','R2C4') ;
    SELECT u.Id
         , u.Col
         , u.ColVal
    FROM #t t
    UNPIVOT
       (ColVal
          FOR COL IN
          ( t.C1
          , t.C2
          , t.C3
          , t.C4
          )
        ) u;
    DROP TABLE #t;

  • April 10, 2018 at 9:13 pm

    #1986250

    Thank you Joe, Awesome, Amazing, Very much appreciated of your expertise. Thanks for your help. You are excellent, Joe.

  • April 10, 2018 at 11:29 pm

    #1986259

    Joe-420121 - Tuesday, April 10, 2018 4:14 PM

    Hello, Please help me with below query, 
    need a row for each cell item, like cartestian product.
    Thanks for your help, Thanks for your time, Very much appreciated of your support.
     
     Create Table #t (
        Id varchar(5)
        , C1 varchar(5)
        , C2 varchar(5)
        , C3 varchar(5)
        , C4 varchar(5)
        )
        select * from #t
        Insert into #t values ('R1', 'R1C1','R1C2','R1C3','R1C4')
        Insert into #t values ('R2', 'R2C1','R2C2','R2C3','R2C4')

        Please help with Query to get result as below, ID    col1    Col2
    R1    C1    R1C1
    R1    C2    R1C2
    R1    C3    R1C3
    R1    C4    R1C4
    R2    C1    R2C1
    R2    C2    R2C2
    R2    C3    R2C3
    R2    C4    R2C4

    Credits to Lynn Pettis


    select id,'c1' as col1,c1 as col2 from t
    union
    select id,'c2' as col1,c2 as col2 from t
    union
    select id,'c3' as col1,c3 as col2 from t
    union
    select id,'c4' as col1,c4 as col2 from t

    Credits to Jacob Wilkins


    select id,col1,col2
    from t
    cross apply (values ('c1',c1),('c2',c2),('c3',c3),('c4',c4))unpvt(col1,col2)
    ;

    Saravanan

  • April 11, 2018 at 6:36 am

    #1986323

    Thanks Saravanatn, Great ideas. Wow. Very much appreciated of your time, Thanks for your help.

  • April 11, 2018 at 6:40 am

    #1986325

    Here's the explanation on the code shown by Saravanan.
    An Alternative (Better?) Method to UNPIVOT (SQL Spackle) - SQLServerCentral
    And obviously, the explanation of UNPIVOT can be found on BOL
    Using PIVOT and UNPIVOT

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 11, 2018 at 10:40 pm

    #1986508

    Just for the fun of it... here's a slightly different syntax...
    CREATE TABLE #t (
        Id VARCHAR (5),
        C1 VARCHAR (5),
        C2 VARCHAR (5),
        C3 VARCHAR (5),
        C4 VARCHAR (5)
    );
    INSERT #t
    VALUES
        ('R1', 'R1C1', 'R1C2', 'R1C3', 'R1C4'),
        ('R2', 'R2C1', 'R2C2', 'R2C3', 'R2C4');

    --======================================================

    SELECT
        t.Id,
        c.Col,
        c.ColVal
    FROM
        #t t
        CROSS APPLY ( VALUES
            ('C1', t.C1),
            ('C2', t.C2),
            ('C3', t.C3),
            ('C4', t.C4)
            ) c (Col, ColVal);

    Results...
    Id    Col  ColVal
    ----- ---- ------
    R1    C1   R1C1
    R1    C2   R1C2
    R1    C3   R1C3
    R1    C4   R1C4
    R2    C1   R2C1
    R2    C2   R2C2
    R2    C3   R2C3
    R2    C4   R2C4

  • April 12, 2018 at 5:45 am

    #1986550

    Thanks Jason, T-SQL has gone very far away from me 🙂 Thanks so much for your inputs, Great to know. Very much appreciated of your time sharing the information.

  • April 12, 2018 at 7:29 am

    #1986583

    Joe-420121 - Thursday, April 12, 2018 5:45 AM

    Thanks Jason, T-SQL has gone very far away from me 🙂 Thanks so much for your inputs, Great to know. Very much appreciated of your time sharing the information.

    No problem. Of course I just realized that this is the same "cross apply values" method that Saravanatn posted above (see the one labeled Jacob Wilkins)...

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

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