Avatar of jfreeman2010
jfreeman2010
Flag for United States of America

asked on 

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;
RMicrosoft SQL ServerStatistical Packages

Avatar of undefined
Last Comment
jfreeman2010

8/22/2022 - Mon