I am trying to get following output but SQL only allows me to perform one value at one time.

This is my current SQL code to use pivot and its returning following output.

    create table temp
(
    PID int,
    category varchar(max),
    score int,
    finalscore int,
)

insert into temp values (1, 'A', 50,20)
insert into temp values (1, 'B', 30,10)
insert into temp values (1, 'C', 40,20)
insert into temp values (1, 'D', 60,70)
insert into temp values (1, 'E', 55,25)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ','   QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


SET @query='select PID,'  @COLS  ' from
        (

select [PID],[Score],category  from temp 
)x
pivot
(
max([Score]) for category in('  @COLS  '))p
'

exec (@query)

drop table temp

CodePudding user response：

You can do this by unpivoting the data before you pivot it, i.e.

SELECT upvt.PID, upvt.category, upvt.Type, upvt.Value 
FROM temp AS t 
UNPIVOT ([Value] FOR [Type] IN (Score, FinalScore)) AS upvt

Which gives:

Then this extra Type column will from an extra row in your pivot. A full example would be:

IF OBJECT_ID(N'tempdb..#temp', 'U') IS NOT NULL
    DROP TABLE #temp;

CREATE TABLE #temp (PID INT, category VARCHAR(MAX), score INT, finalscore INT, );

INSERT INTO #temp (PID, category, score, finalscore)
VALUES
    (1, 'A', 50, 20),
    (1, 'B', 30, 10),
    (1, 'C', 40, 20),
    (1, 'D', 60, 70),
    (1, 'E', 55, 25);


DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT DISTINCT  ','   QUOTENAME(c.category)FROM #Temp AS C FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = N'select PID, [Type], '   @cols   N' from
        (
        SELECT upvt.PID, upvt.category, upvt.Type, upvt.Value 
        FROM #temp AS t 
        UNPIVOT ([Value] FOR [Type] IN (Score, FinalScore)) AS upvt
        )x
        PIVOT (MAX([Value]) FOR category IN ('   @cols   N'))p
        ';

EXECUTE sp_executesql @query;