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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfreeman2010Author Commented:
ok, thanks
0
jfreeman2010Author Commented:
thanks for help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
R

From novice to tech pro — start learning today.