Dynamic Pivot Table with Multiple Columns in SQL Server

SQL Server : dynamic pivot over 5 columns

In order to get the result, you will need to look at unpivoting the data in the Total and Volume columns first before applying the PIVOT function to get the final result. My suggestion would be to first write a hard-coded version of the query then convert it to dynamic SQL.

The UNPIVOT process converts these multiple columns into rows. There are a few ways to UNPIVOT, you can use the UNPIVOT function or you can use CROSS APPLY. The code to unpivot the data will be similar to:

select id, 
    col = cast(t_year as varchar(4))+'_'+t_type+'_'+col, 
    value
from ATM_TRANSACTIONS t
cross apply
(
    select 'total', total union all
    select 'volume', volume
) c (col, value);

This gives you data in the format:

+-----+---------------+-------+
| id  |      col      | value |
+-----+---------------+-------+
| DD1 | 2008_A_total  |  1000 |
| DD1 | 2008_A_volume |    10 |
| DD1 | 2008_B_total  |  2000 |
| DD1 | 2008_B_volume |    20 |
| DD1 | 2008_C_total  |  3000 |
| DD1 | 2008_C_volume |    30 |
+-----+---------------+-------+

Then you can apply the PIVOT function:

select ID, 
    [2008_A_total], [2008_A_volume], [2008_B_total], [2008_B_volume],
    [2008_C_total], [2008_C_volume], [2009_A_total], [2009_A_volume]
from
(
    select id, 
        col = cast(t_year as varchar(4))+'_'+t_type+'_'+col, 
        value
    from ATM_TRANSACTIONS t
    cross apply
    (
        select 'total', total union all
        select 'volume', volume
    ) c (col, value)
) d
pivot
(
    max(value)
    for col in ([2008_A_total], [2008_A_volume], [2008_B_total], [2008_B_volume],
                [2008_C_total], [2008_C_volume], [2009_A_total], [2009_A_volume])
) piv;

Now that you have the correct logic, you can convert this to dynamic SQL:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(cast(t_year as varchar(4))+'_'+t_type+'_'+col) 
                    from ATM_TRANSACTIONS t
                    cross apply
                    (
                        select 'total', 1 union all
                        select 'volume', 2
                    ) c (col, so)
                    group by col, so, T_TYPE, T_YEAR
                    order by T_YEAR, T_TYPE, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id,' + @cols + ' 
            from 
            (
                select id, 
                    col = cast(t_year as varchar(4))+''_''+t_type+''_''+col, 
                    value
                from ATM_TRANSACTIONS t
                cross apply
                (
                    select ''total'', total union all
                    select ''volume'', volume
                ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

This will give you a result:

+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
| id  | 2008_A_total | 2008_A_volume | 2008_B_total | 2008_B_volume | 2008_C_total | 2008_C_volume | 2009_A_total | 2009_A_volume | 2009_B_total | 2009_B_volume | 2009_C_total | 2009_C_volume |
+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
| DD1 |         1000 |            10 |         2000 |            20 |         3000 |            30 |         4000 |            40 |         5000 |            50 |         6000 |            60 |
| DD2 |         7000 |            70 |         8000 |            80 |         9000 |            90 |        10000 |           100 |        11000 |           110 |         1200 |           120 |
+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+

SQL dynamic pivot for multiple columns

Just a minor twist to your original. Notice the sub-query/Union All.

Declare @SQL varchar(max)
Select  @SQL = Stuff((Select Distinct 
                             ',' + QuoteName(Symbol+'_Price') 
                           + ',' + QuoteName(Symbol+'_Quantity') 
                      From   Opt For XML Path('')),1,1,'')   
Select  @SQL = 'Select Date,' + @SQL + ' 
                From (
                      Select Date,Item=Symbol+''_Price'',Val=Price From Opt
                      Union All
                      Select Date,Item=Symbol+''_Quantity'',Val=Quantity From Opt
                     ) A
                Pivot (max(Val) For Item in (' + @SQL + ') ) p'

Exec(@SQL);

Returns

Date         apple_Price    apple_Quantity  banana_Price    banana_Quantity
2016-11-14   30             15              22              20

SQL Server dynamic pivot with multiple columns

The following is the stored procedure used for the bulk of my dynamic pivots

Exec [prc-Pivot] 'tbl1','Country','sum(Sales)[]','IssuedOn','sum(Transactions)[Transactions],sum(Sales)[TotalSales]'

IssuedOn    Transactions    TotalSales  Australia   Canada  India
2016-08-29  14              70          15          15      40
2016-08-30  17              85          25          10      50
2016-08-31  47              185         30          55      100

The stored procedure

ALTER PROCEDURE [dbo].[prc-Pivot] (
    @Source varchar(1000),          -- Any Table or Select Statement
    @PvotCol varchar(250),          -- Field name or expression ie. Month(Date)
    @Summaries varchar(250),        -- aggfunction(aggValue)[optionalTitle]
    @GroupBy varchar(250),          -- Optional additional Group By 
    @OtherCols varchar(500) )       -- Optional Group By or aggregates
AS

--Exec [prc-Pivot] 'Select Year=Year(TR_Date),* From [Chinrus-Series].[dbo].[DS_Treasury_Rates]','''Q''+DateName(QQ,TR_Date)','avg(TR_Y10)[-Avg]','Year','count(*)[Records],min(TR_Y10)[Min],max(TR_Y10)[Max],Avg(TR_Y10)[Avg]'
--Exec [prc-Pivot] '#Temp','Attribute','max(Description)[]','ID','count(*)[Records]'

Set NoCount On

Declare @Vals varchar(max),@SQL varchar(max);
Set @Vals = ''
Set @OtherCols= IsNull(', ' + @OtherCols,'')
Set @Source = case when @Source Like 'Select%' then @Source else 'Select * From '+@Source end
Create Table #TempPvot  (Pvot varchar(100))
Insert Into #TempPvot
Exec ('Select Distinct Convert(varchar(100),' + @PvotCol + ') as Pvot FROM (' + @Source + ') A')
--Select @Vals = @Vals + ', isnull(' + Replace(Replace(@Summaries,'(','(CASE WHEN ' + @PvotCol + '=''' + Pvot +  ''' THEN '),')[', ' END),NULL) As [' + Pvot ) From #TempPvot Order by Pvot
  Select @Vals = @Vals + ', isnull(' + Replace(Replace(@Summaries,'(','(CASE WHEN ' + @PvotCol + '=''' + Pvot +  ''' THEN '),')[', ' END),0) As [' + Pvot ) From #TempPvot Order by Pvot
Drop Table #TempPvot
Set @SQL = Replace('Select ' + Isnull(@GroupBy,'') + @OtherCols + @Vals + ' From (' + @Source + ') PvtFinal ' + case when Isnull(@GroupBy,'')<>'' then 'Group By ' + @GroupBy + ' Order by ' + @GroupBy else '' end,'Select , ','Select ')
--Print @SQL
Exec (@SQL)

Dynamic Pivot multiple columns in SQL Server

ok, I created a temp table to do some testing on. The solution requires an unpivot first. I recommend running with/without the extra test data to get a sense of some other behaviors that surround this solution -- the weirdness that comes with the MAX aggregation and lack of new rows that you might have expected when changing the value in 'name'.

GL. Hope it helps.

-------------------------
-- Some test data here
CREATE table #addresses ( Id int, Name varchar(5), FromAddress varchar(5), ToAddress varchar(5))
insert into #addresses(id, Name, FromAddress, ToAddress) Values
(1,'Joey', 'ABC', 'JKL')
, (2,'Joey', 'DEF', 'MNO')
, (3,'Joey', 'GHI', 'PQR')
, (4,'Spike', 'XXX', 'YYY')
, (1,'Spike', 'QQQ', 'RRR')

-------------------------
--  Solution starts here.  create a temp table and unpivot your data into it.
--  Your initial technique of does not work, PIVOT only supports one aggregation

CREATE table #unpvt(RowColCode varchar(20), vals varchar(20))
Insert into #unpvt
SELECT ColCode + '_' + Cast(ID as varchar(2)) as RowColCode, vals
FROM #addresses a
UNPIVOT
    (vals for ColCode in (Name,FromAddress,ToAddress)) c

-------------------------
--  Read the temp table for a column list

declare @ColList nvarchar(max)
set @ColList = STUFF((
    SELECT distinct ',[' + t.RowColCode + ']'
            FROM #unpvt t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

-------------------------
-- 're pivot' the data using your new column list

declare @qry varchar(max)
set @qry = '

select *
from
    #unpvt
    PIVOT(
    MAX(vals)
    FOR RowColCode in (' +@ColList +  ') 
    ) rslt
'

execute(@qry)

TSQL - dynamic Pivot on multiple columns

Try the following:

--Data
DROP TABLE IF EXISTS TEMP
DROP TABLE IF EXISTS test

CREATE TABLE test
    ([Product] varchar(5), [Year] varchar(4),[Measure] varchar(10), 
     [1] int, [2] int, [3] int, [4] int,[5] int, [6] int,[7] int, [8] int,[9] int, [10] int,[11] int, [12] int)
;

INSERT INTO test
    ([Product], [Year], [Measure], [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
VALUES
    ('ABCD', '2017','efgh', 1, 11,21,31,41,51,61,71,81,91,100,110),
    ('ABCD', '2017','efgh', 2, 12,21,31,41,51,61,71,81,91,100,110),
    ('ABCD', '2018','mnop', 3, 13,21,31,41,51,61,71,81,91,100,110),
    ('ABCD', '2018','mnop', 4, 14,21,31,41,51,61,71,81,91,100,110),
    ('WXYZ', '2017','efgh', 5, 15,21,31,41,51,61,71,81,91,100,110),
    ('WXYZ', '2017','efgh', 6, 16,21,31,41,51,61,71,81,91,100,110),
    ('WXYZ', '2018','mnop', 7, 17,21,31,41,51,61,71,81,91,100,110),
    ('WXYZ', '2018','mnop', 8, 18,21,31,41,51,61,71,81,91,100,110)
;

--UNPIVOTING MONTHS (As fixed months that is why using non-dynamic unpivot)
DROP TABLE IF EXISTS TEMP
SELECT Product, [Year], Measure, [Month], [Value]
INTO TEMP
FROM
(
  SELECT Product, [Year], Measure
  ,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
   FROM test
) AS t
UNPIVOT
(
  [Value] FOR [Month] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
 ) AS up;

select * from test

--Dynamic  Pivoting
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
                 (
                     SELECT DISTINCT 
                            ','+ Measure
                     FROM TEMP c FOR XML PATH(''), TYPE
                 ).value('.', 'nvarchar(max)'), 1, 1, '');
print @cols
SET @query = 'SELECT Product, Year, [Month], '+@cols+' from 
                (SELECT 
                   Product,
                   Year,
                   [Month],
                   [Value],
                   Measure AS Category
                FROM TEMP
                )x 
                pivot 
                (
                    sum([Value]) for Category in ('+@cols+')
                ) p
                order by 1,2, convert(int, [Month])';
print @query
EXECUTE (@query);

DROP TABLE IF EXISTS TEMP
DROP TABLE IF EXISTS test

SQL Server Pivot on Multiple columns and dynamic column names

You don't really want to use PIVOT here explicitly because it is meant to pivot one column, not multiple. Conditional aggregation like in @D-Shih's answer is the way you want to go, except you can't do that if you expect the query to change with the data. So you can use dynamic SQL:

DECLARE @s0  nvarchar(max) = N'',
        @s1  nvarchar(max) = N'', 
        @s2  nvarchar(max) = N'', 
        @s3  nvarchar(max) = N'', 
        @sql nvarchar(max) = N'';

;WITH cols AS 
(
  SELECT fnd, efnd = char(39) + fnd + char(39) FROM #s
)
SELECT @s0 += N',
   acc_' + fnd + N'     '
            + N' = MAX(CASE fnd WHEN ' + efnd 
            + N' THEN acc      END)',
       @s1 += N',
   pr_' + fnd + N'      '
            + N' = SUM(CASE fnd WHEN ' + efnd 
            + N' THEN pr       ELSE 0 END)',
       @s2 += N',
   pos_live_' + fnd 
            + N' = SUM(CASE fnd WHEN ' + efnd 
            + N' THEN pos_live ELSE 0 END)',
       @s3 += N',
   pos_yest_' + fnd 
            + N' = SUM(CASE fnd WHEN ' + efnd 
            + N' THEN pos_yest ELSE 0 END)'
FROM cols GROUP BY fnd, efnd;

SET @sql += N'SELECT grp, id' + @s0 + @s1 + @s2 + @s3 + N'
FROM #s GROUP BY grp, id ORDER BY grp;';
          
PRINT @sql;
EXEC sys.sp_executesql @sql;

Print output:

SELECT grp, id,
   acc_EU      = MAX(CASE fnd WHEN 'EU' THEN acc      END),
   acc_KY      = MAX(CASE fnd WHEN 'KY' THEN acc      END),
   acc_UC      = MAX(CASE fnd WHEN 'UC' THEN acc      END),
   acc_US      = MAX(CASE fnd WHEN 'US' THEN acc      END),
   pr_EU       = SUM(CASE fnd WHEN 'EU' THEN pr       ELSE 0 END),
   pr_KY       = SUM(CASE fnd WHEN 'KY' THEN pr       ELSE 0 END),
   pr_UC       = SUM(CASE fnd WHEN 'UC' THEN pr       ELSE 0 END),
   pr_US       = SUM(CASE fnd WHEN 'US' THEN pr       ELSE 0 END),
   pos_live_EU = SUM(CASE fnd WHEN 'EU' THEN pos_live ELSE 0 END),
   pos_live_KY = SUM(CASE fnd WHEN 'KY' THEN pos_live ELSE 0 END),
   pos_live_UC = SUM(CASE fnd WHEN 'UC' THEN pos_live ELSE 0 END),
   pos_live_US = SUM(CASE fnd WHEN 'US' THEN pos_live ELSE 0 END),
   pos_yest_EU = SUM(CASE fnd WHEN 'EU' THEN pos_yest ELSE 0 END),
   pos_yest_KY = SUM(CASE fnd WHEN 'KY' THEN pos_yest ELSE 0 END),
   pos_yest_UC = SUM(CASE fnd WHEN 'UC' THEN pos_yest ELSE 0 END),
   pos_yest_US = SUM(CASE fnd WHEN 'US' THEN pos_yest ELSE 0 END)
FROM #s GROUP BY grp, id ORDER BY grp;

Execution results:

grp	id	acc_EU	acc_KY	acc_UC	acc_US	pr_EU	pr_KY	pr_UC	pr_US	pos_live_EU	pos_live_KY	pos_live_UC	pos_live_US	pos_yest_EU	pos_yest_KY	pos_yest_UC	pos_yest_US
GR1	FVS1	EU1	CFD01	UCD01	null	23.5	24	23	0	1	1	1	0	1	1	1	0
GR1	VX1	null	CFD01	UCD01	US1	0	25	24.5	25	0	100	30	10	0	95	20	95
GR1	VX2	null	CFD01	UCD01	null	0	20	19	0	0	10	5	0	0	10	5	0
GR2	FVS1	EU2	CFD02	UCD02	null	23.5	24	23	0	10	10	10	0	10	10	10	0

Dynamic pivot using multiple columns where derived value may appear more than once

I wonder why the "fairly new" people always tend to start with the more complex stuff like dynamic SQL? Anyway, your solution is close to working and the mistakes are easy enough to fix.

General guideline for dynamic SQL: build the solution in steps and print the constructed query (part) for validation during its construction. This allows you to execute the constructed query as well to validate the result.

Errors

No column name was specified for column 4 of 'D'.

Your subquery D within the dynamic SQL query is missing a column name or alias. Add an alias with as.

The column 'SYS40' was specified multiple times for 'P'.

The way you build up the value for @JobEventKey will produce a string like [SYS10], [SYS40], [SYS40], .... The pivot column list from your pivoting result P expects unique column names. Add a group by to avoid duplicates.

DECLARE @JobEventKey NVARCHAR(MAX) = ''
DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @JobEventKey += QUOTENAME(cls + CAST(cde AS VARCHAR(10))) + ','
FROM jobevt

WHERE jobevt.num = '3177564'
--> num=3177564 contains multiple rows for "[SYS40]"
--> the contatenations contains duplicates!

SET @JobEventKey = LEFT(@JobEventKey, LEN(@JobEventKey) -1)

SET @SQL = 

'SELECT * FROM
    (SELECT
         num
        ,dat
        ,cde
        ,cls + CAST(cde AS VARCHAR(10)) --> 4th column missing a name / alias!
    FROM
        jobevt
    WHERE
        (num = 3177564)
) AS D

PIVOT (
    MAX(dat)
    FOR cde IN (' + @JobEventKey +
    ')
) AS P'

EXECUTE sp_executesql @SQL

Improved version

Minor tweaks:

Replaced nvarchar(max) with something more reasonable (depends on your data).
Use table aliases where possible.
Do not place quotes around integer values (for integer type columns).
Add order by for sorted columns in after pivoting.

Final, working query:

DECLARE @JobEventKey NVARCHAR(1000) = '';

SELECT @JobEventKey += QUOTENAME(je.cls + CONVERT(NVARCHAR(10), je.cde)) + ','
FROM jobevt je
WHERE je.num = 3177564
group by QUOTENAME(je.cls + CONVERT(NVARCHAR(10), je.cde))
order by QUOTENAME(je.cls + CONVERT(NVARCHAR(10), je.cde));

SET @JobEventKey = LEFT(@JobEventKey, LEN(@JobEventKey)-1);

-- validate keys
select @JobEventKey;

DECLARE @sql NVARCHAR(2000) = 
'SELECT P.*
 FROM ( SELECT je.num
              ,je.dat
              ,je.cls + CONVERT(NVARCHAR(10), cde) as clscde
        FROM jobevt je
        WHERE je.num = 3177564 ) AS d
 PIVOT (MAX(d.dat) FOR d.clscde IN (' + @JobEventKey + ')) AS P'

-- validate sql
select @sql;

-- execute
EXECUTE sp_executesql @sql;

Result

Data used to replicate:

create table jobevt
(
  num int,
  dat date,
  cls nvarchar(3),
  cde int
);

insert into jobevt (num, dat, cls, cde) values
(3177564, '2021-02-24', 'SYS',  10),
(3177564, '2021-02-24', 'SYS',  40),
(3177564, '2021-02-24', 'SYS',  40),
(3177564, '2021-02-24', 'SYS',  40),
(3177564, '2021-02-24', 'SYS',  42),
(3177564, '2021-02-24', 'SYS',  60),
(3177564, '2021-02-24', 'SYS',  60),
(3177564, '2021-02-24', 'SYS',  89),
(3177564, '2021-02-24', 'SYS', 100),
(3177564, '2021-02-24', 'SYS', 115),
(3177564, '2021-02-24', 'GEN', 120),
(3177564, '2021-03-01', 'GEN', 120),
(3177564, '2021-02-26', 'GEN', 160),
(3177564, '2021-02-24', 'SYS', 198),
(3177564, '2021-02-24', 'GEN', 210),
(3177564, '2021-02-26', 'GEN', 220),
(3177564, '2021-02-24', 'GEN', 310),
(3177564, '2021-02-26', 'GEN', 310),
(3177564, '2021-02-24', 'SYS', 422);

Result:

num      GEN120      GEN160      GEN210      GEN220      GEN310      SYS10       SYS100      SYS115      SYS198      SYS40       SYS42       SYS422      SYS60       SYS89
-------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
3177564  2021-03-01  2021-02-26  2021-02-24  2021-02-26  2021-02-26  2021-02-24  2021-02-24  2021-02-24  2021-02-24  2021-02-24  2021-02-24  2021-02-24  2021-02-24  2021-02-24

Fiddle to see things in action.