SQL Pivot to create 2 columns per row

Question

I have a table that looks like this:

Date	Name	Actual	Target
2023-03-14	Bob	15	15
2023-03-14	Jim	9	5
2023-03-14	Steve	10	9
2023-03-15	Bob	11	11
2023-03-15	Jim	16	16
2023-03-15	Steve	5	12

The Name column isn't always the same depending on who is working so the pivot has to be dynamic. I want the table to look like this:

Date	Bob_Actual	Bob_Target	Jim_Actual	Jim_Target	Steve_Actual	Steve_Target
2023-03-14	15	15	9	5	10	9
2023-03-15	11	11	16	16	5	12

I was able to get this desired result if I just dynamically pivot on target, but I can't figure out how to pivot both the target and the actual

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

select @cols = STUFF((SELECT ',' + QUOTENAME(Name) 
                    from yt
                    group by Name
                    order by Name
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Date,' + @cols + ' from 
             (
                select Date, Name, Target
                from yt
            ) x
            pivot 
            (
                sum(Target)
                for Name in (' + @cols + ')
            ) p '

execute(@query);

I used the above code to get it to work for just target, but am not sure how to add actual

Answer 1

Don't use pivots, they're not flexible enough, try conditional aggregation instead:

SELECT  *
INTO #yt
FROM    (
    VALUES  (N'2023-03-14', N'Bob', 15, 15)
    ,   (N'2023-03-14', N'Jim', 9, 5)
    ,   (N'2023-03-14', N'Steve', 10, 9)
    ,   (N'2023-03-15', N'Bob', 11, 11)
    ,   (N'2023-03-15', N'Jim', 16, 16)
    ,   (N'2023-03-15', N'Steve', 5, 12)
) t (Date,Name,Actual,Target)

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

SELECT  @cols = (   
    SELECT  '
    ,   SUM(case when name = ' + QUOTENAME(Name, '''') + ' then Actual END) AS ' + QUOTENAME(Name + '_Actual') + N'
    ,   SUM(case when name = ' + QUOTENAME(Name, '''') + ' then Target END) AS ' + QUOTENAME(Name + '_Targer')
    FROM    #yt
    GROUP BY Name
    ORDER BY Name
    FOR XML PATH(''), TYPE
            ).value('text()[1]', 'NVARCHAR(MAX)') 

SET @query = '
    SELECT  Date' + @cols + '
    FROM    #yt
    GROUP BY Date
            '
EXEC(@query);

This way, you have much more flexibility in handling both column names and values. The SUM(CASE WHEN ...) is the conditional aggregation part that fetches the needed values and places them in correct columns.