Need help on t-sql 2012

I have the following query on prior help and I added a column 'text1', change the Comp5 heading to Comp15 and I can't get 'text1' and Comp15 values on the result:
DECLARE @TEMP4 TABLE
    (
        PID INT ,
        TEXT1 VARCHAR(10),
Comp1 VARCHAR(10),
 Comp2 VARCHAR(10),
Comp3 VARCHAR(10),
Comp4 VARCHAR(10),
Comp15 VARCHAR(10)
    );

INSERT INTO @TEMP4
VALUES ( 11122, '1212', NULL, NULL, NULL, NULL, NULL ) ,
         ( 12345, NULL, NULL, NULL, '123', NULL, NULL ) ,
       ( 23456, NULL, '234', NULL, 'ewr', NULL, NULL ) ,
       ( 34567, NULL, NULL, 'acc', NULL, NULL, 'def' ) ,
       ( 45678, NULL, NULL, NULL, 'jkl', NULL, NULL ) ,
       ( 56789, NULL, NULL, NULL, NULL, NULL, 'we1' ) ,
       ( 23450, NULL, 'abc', 'acc', 'exy', 'ert', 'def' );

WITH Unpivoted
AS ( SELECT *
     FROM   @TEMP4 T UNPIVOT(CompValue FOR CompType IN(Comp1, Comp2, Comp3, Comp4, Comp15)) U ) ,
     Reordered  
AS ( SELECT U.PID ,
                  U.TEXT1,
            U.CompValue ,
            'Comp' + CAST(ROW_NUMBER() OVER ( PARTITION BY U.PID
                                              ORDER BY U.CompType ASC ) AS VARCHAR(255)) AS NewCompType
     FROM   Unpivoted U )
SELECT *
FROM   Reordered R
    PIVOT (   MIN(CompValue)
              FOR NewCompType IN ( Comp1, Comp2, Comp3, Comp4, Comp15 )) P;
jfreeman2010Asked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
can we include this in the Unpivot (comp*) + text1 cols ?
Unfortunately, no. You need to stick with the UNION solution.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't get the comp15 column because of the following:
'Comp' + CAST(ROW_NUMBER() OVER ( PARTITION BY U.PID
                             ORDER BY U.CompType ASC ) AS VARCHAR(255))

Open in new window

That part of code will return comp1, comp2, ..., comp5 as you don't have 15 comp columns it will never reach comp15.

But with a small code update it works:
DECLARE @TEMP4 TABLE
    (
    PID INT ,
    TEXT1 VARCHAR(10),
	Comp1 VARCHAR(10),
	Comp2 VARCHAR(10), 
	Comp3 VARCHAR(10),
	Comp4 VARCHAR(10),
	Comp15 VARCHAR(10)
    );

INSERT INTO @TEMP4
VALUES ( 11122, '1212', NULL, NULL, NULL, NULL, NULL ) ,
       ( 12345, NULL, NULL, NULL, '123', NULL, NULL ) ,
       ( 23456, NULL, '234', NULL, 'ewr', NULL, NULL ) ,
       ( 34567, NULL, NULL, 'acc', NULL, NULL, 'def' ) ,
       ( 45678, NULL, NULL, NULL, 'jkl', NULL, NULL ) ,
       ( 56789, NULL, NULL, NULL, NULL, NULL, 'we1' ) ,
       ( 23450, NULL, 'abc', 'acc', 'exy', 'ert', 'def' );

WITH Unpivoted
AS ( SELECT *
     FROM   @TEMP4 T UNPIVOT(CompValue FOR CompType IN(Comp1, Comp2, Comp3, Comp4, Comp15)) U ) ,
     Reordered   
AS ( SELECT U.PID ,
            U.TEXT1,
            U.CompValue ,
            'Comp' + CAST(ROW_NUMBER() OVER ( PARTITION BY U.PID
                                              ORDER BY U.CompType ASC ) AS VARCHAR(255)) AS NewCompType
     FROM   Unpivoted U )
SELECT *
FROM   Reordered R
    PIVOT (   MIN(CompValue)
              FOR NewCompType IN ( Comp1, Comp2, Comp3, Comp4, Comp5 )) P; 

Open in new window

0
 
jfreeman2010Author Commented:
HI Vitor Montalvão - think you very much for your help!!

That works for all the compx columns.  but the 'text1'  column did not get the value and that row also missing from the query.  please help.

Also I can insert the result of this query to a temp table, right ??
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Apologizes. Somehow I've missed your feedback.
Can you provide an example for "the 'text1'  column did not get the value and that row also missing from the query"?

Also I can insert the result of this query to a temp table, right ??
Right.
0
 
jfreeman2010Author Commented:
Hi Vitor - here are the temp tb and see field/col text1 with first rec in insert PID=11122, the result exclude this col:

DECLARE @TEMP4 TABLE
    (
        PID INT ,
        TEXT1 VARCHAR(10),
Comp1 VARCHAR(10),
 Comp2 VARCHAR(10),
Comp3 VARCHAR(10),
Comp4 VARCHAR(10),
Comp15 VARCHAR(10)
    );

INSERT INTO @TEMP4
VALUES ( 11122, '1212', NULL, NULL, NULL, NULL, NULL ) ,
         ( 12345, NULL, NULL, NULL, '123', NULL, NULL ) ,
       ( 23456, NULL, '234', NULL, 'ewr', NULL, NULL ) ,
       ( 34567, NULL, NULL, 'acc', NULL, NULL, 'def' ) ,
       ( 45678, NULL, NULL, NULL, 'jkl', NULL, NULL ) ,
       ( 56789, NULL, NULL, NULL, NULL, NULL, 'we1' ) ,
       ( 23450, NULL, 'abc', 'acc', 'exy', 'ert', 'def' );

WITH Unpivoted
AS ( SELECT *
     FROM   @TEMP4 T UNPIVOT(CompValue FOR CompType IN(Comp1, Comp2, Comp3, Comp4, Comp15)) U ) ,
     Reordered  
AS ( SELECT U.PID ,
                  U.TEXT1,
            U.CompValue ,
            'Comp' + CAST(ROW_NUMBER() OVER ( PARTITION BY U.PID
                                              ORDER BY U.CompType ASC ) AS VARCHAR(255)) AS NewCompType
     FROM   Unpivoted U )
SELECT *
FROM   Reordered R
    PIVOT (   MIN(CompValue)
              FOR NewCompType IN ( Comp1, Comp2, Comp3, Comp4, Comp15 )) P;
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I see. That happens because for that row, Comp1, Comp2, Comp3, Comp4 and Comp15 fields are all NULL values so the row is excluded during the UNPIVOT part:
WITH Unpivoted
AS ( SELECT *
     FROM @TEMP4 T UNPIVOT(CompValue FOR CompType IN(Comp1, Comp2, Comp3, Comp4, Comp15)) U ) ,

Open in new window

0
 
jfreeman2010Author Commented:
anyway can include that row or I had to union the output from UNPIVOT ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That row will be included if you have at least one value (in your example it's all NULL).
If you really want to consider this situation then I think only the UNION can help you.
0
 
jfreeman2010Author Commented:
VALUES ( 11122, '1212', NULL, NULL, NULL, NULL, NULL )  this row had 2 cols had values.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
this row had 2 cols had values.
But aren't the columns used by the UNPIVOT statement (comp*). Those are the ones that really matter.
0
 
jfreeman2010Author Commented:
can we include this in the Unpivot (comp*) + text1 cols ?
0
 
jfreeman2010Author Commented:
ok, thanks
0
 
jfreeman2010Author Commented:
thanks for help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.