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;