Query Help on PIVOT

Question

Query Help on PIVOT

Joe-420121

Hall of Fame

Points: 3152
More actions
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

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

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

Joe Torre SSChampion Points: 10248 More actions · Answer 1

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;

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 2

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

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 3

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

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 4

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 6

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

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 7

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.

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 8

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)...