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)
SQL Server: Dynamic Pivot for multiple columns and sequenced number for columns
Here you are
create table MyTable
(
accdate date,
account varchar(100),
accountdescription varchar(100),
amount bigint,
vatvalue int,
calcLevel varchar(100),
calcObject varchar(100)
)
insert into MyTable
values
('20200831', 4101, 'purchaces',33750,4725,'first','8309'),
('20200831', 4101, 'purchaces',33750,4725,'second','110'),
('20200831', 4101, 'purchaces',33750,4725,'third','111'),
('20200831', 2660, 'VAT',4725,0,null,null)
select * from MyTable
declare @sql nvarchar(max)
declare @cols nvarchar(max)
declare @colsLevel nvarchar(max)
declare @colsObject nvarchar(max)
select @cols = STUFF((SELECT ',' + QUOTENAME(calcLevel) +','+ QUOTENAME(CalcObject)
from mytable t
where calcLevel is not null
group by CalcLevel, CalcObject
order by CalcLevel, CalcObject
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @cols
select @colsLevel = STUFF((SELECT ',''' + calcLevel + ''' as ' + QUOTENAME('CalcLevel'+convert(varchar(100), ROW_NUMBER() OVER(ORDER BY CalcLevel asc)))
from mytable t
where calcLevel is not null
group by CalcLevel
order by CalcLevel
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsLevel
select @colsObject = STUFF((SELECT ',''' + CalcObject + ''' as ' +QUOTENAME('CalcObject'+convert(varchar(100),ROW_NUMBER() OVER(ORDER BY CalcObject asc)))
from mytable t
where CalcObject is not null
group by CalcObject
order by CalcObject
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsObject
set @sql =
'SELECT AccDate, AccountDescription, Amount, VatValue, ' + @colsLevel + ', ' + @colsObject + '
FROM
( select AccDate, AccountDescription, Account, Amount, VatValue, CalcLevel
from mytable t
) AS s
PIVOT
(
min(CalcLevel)
FOR Account IN (' + @cols + ')
) AS pvt
'
print (@sql)
EXEC(@sql)
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 Dynamic pivot for an unknow number of columns
Try this, It follows the same example mentioned here:Convert Rows to columns using 'Pivot' in SQL Server
--Drop Sample temp Table
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
BEGIN
DROP TABLE #temp2
END
--create Sample temp Table
create Table #temp2
(
[name] varchar(255),
Item varchar(255),
note varchar(255)
)
--Insert Sample Data
insert into #temp2
values( 'George','Paperclip','Two boxes'),
('George','Stapler','blue one'),
('George','Stapler','red one'),
('George','Desk lamp','No light bulb'),
('Mark','Paperclip','One box 2'),
('Mark','Paperclip','One box 4'),
('Mark','Block Notes','a blue one')
DECLARE @cols AS NVARCHAR(MAX), @cols2 AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
--Generate Columns from Data
--Generate Columns from Data
select @cols = STUFF((SELECT ', isnull(' + QUOTENAME(Item) + ',0) as' + QUOTENAME(Item)
from #temp2
group by Item
order by Item
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @cols2 = STUFF((SELECT ', ' + QUOTENAME(Item)
from #temp2
group by Item
order by Item
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--Pivot Query
set @query = 'SELECT [name],' + @cols + ' from
(
select [Name], Item, count(*) as xcount
from #temp2
group by Name, Item
) x
pivot
(
sum(xCount)
for Item in (' + @cols2+ ')
) p '
execute(@query);
--Drop Sample Temp Table
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
BEGIN
DROP TABLE #temp2
END
Combination of dynamic pivot and static pivot in SQL Server
I have used your static pivot part of the query as the source of dynamic pivot. Create two sets of dynamic pivot column list. One for pivoting and the another with Coalesce() to select pivoted columns (to convert null into 0). If there is no categcount for any category then that category has been replaced with null (case when). Two more aliases for Category and SumCatCount have been created since those were used in pivot condition.
Here goes your answer:
create table #temp
(
Place nvarchar(20),
State nvarchar(20),
Category nvarchar(20) null,
CategCount int null,
MCount int null,
Buys int,
Cost int
)
insert into #temp values ('London', 'UK', 'Old', 3, NULL, 22, 4.50)
insert into #temp values ('London', 'UK', 'Old', 6, 5, 3, 22.00)
insert into #temp values ('Brussels', 'BE', 'Young', 2, NULL, 4, 3.50)
insert into #temp values ('Brussels', 'BE', 'M', NULL, 5, 12, 1.20)
insert into #temp values ('Brussels', 'BE', 'M', NULL, 2, 1, 1.20)
DECLARE @cols AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
DECLARE @colsForSelect AS NVARCHAR(MAX)='';
SET @cols = STUFF((SELECT distinct ',' + quotename(category)
FROM #temp where CategCount is not null
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @colsForSelect = STUFF((SELECT distinct ',' + ' Coalesce('+quotename(category)+',0) '+ quotename(category)
FROM #temp where CategCount is not null
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--select (@cols) as bm
set @query =
'SELECT count,place,state,(case when OldSumCatCount >0 then OldCategory else null end)Category,SumMCount, ' + @colsForSelect + ',SumCost,SumBuys from
(
select count(*) as count, place, state,category OldCategory, category,
sum(ISNULL(MCount, 0)) as SumMCount,
sum(ISNULL(CategCount, 0)) as OldSumCatCount,
sum(ISNULL(CategCount, 0)) as SumCatCount,
sum(Cost) as SumCost,
sum(ISNULL(buys, 0)) as SumBuys
from #temp
group by place , state, category
) src
pivot
(
max(SumCatCount) for Category in (' + @cols + ')
) piv
order by place desc,count'
execute(@query)
GO
| count | place | state | Category | SumMCount | Old | Young | SumCost | SumBuys |
|---|---|---|---|---|---|---|---|---|
| 2 | London | UK | Old | 5 | 9 | 0 | 26 | 25 |
| 1 | Brussels | BE | Young | 0 | 0 | 2 | 3 | 4 |
| 2 | Brussels | BE | null | 7 | 0 | 0 | 2 | 13 |
Dynamic Pivot Sql Query display all from one table
You can reconstruct the query
SELECT *
FROM
(
SELECT A.[Phone], A.[CustNo], A.[Name], C.[BrandName], B.[qty]
FROM [Table-A] AS A
LEFT JOIN [Table-B] AS B
ON A.[CustNo] = B.[CustNo]
AND B.[odate] = '2021-04-22'
LEFT JOIN [Table-C] AS C on C.productid = B.Productid
WHERE A.[Route] = 1
) t
PIVOT
(
MIN([qty]) FOR [BrandName] IN ([Brand-1],[Brand-2],[Brand-3])
) AS piv
which contains LEFT JOIN rather than INNER JOIN, and STRING_AGG() function in order to generate the pivoted columns dynamically as in the following code block
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
SET @cols = ( SELECT STRING_AGG(QUOTENAME([BrandName]),',')
FROM (SELECT DISTINCT [BrandName]
FROM [Table-C] ) C );
SET @query =
N'SELECT *
FROM
(
SELECT A.[Phone], A.[CustNo], A.[Name], C.[BrandName], B.[qty]
FROM [Table-A] AS A
LEFT JOIN [Table-B] AS B
ON A.[CustNo] = B.[CustNo]
AND B.[odate] = ''2021-04-22''
LEFT JOIN [Table-C] AS C on C.productid = B.Productid
WHERE A.[Route] = 1
) t
PIVOT
(
MIN([qty]) FOR [BrandName] IN (' + @cols + N')
) AS piv'
EXEC sp_executesql @query;
Demo
Related Topics
Difference Between a User and a Login in SQL Server
Does Foreign Key Improve Query Performance
Mixing Implicit and Explicit Joins
Inner Join VS Natural Join VS Using Clause: Are There Any Advantages
How to Log SQL Statements in Grails
Using Excel Vba to Export Data to Ms Access Table
How Can Multiple Rows Be Concatenated into One in Oracle Without Creating a Stored Procedure
Decision Between Storing Lookup Table Id'S or Pure Data
SQL Server Convert Varchar to Datetime
Convert Integer to Hex and Hex to Integer
Differencebetween Using a Cross Join and Putting a Comma Between the Two Tables
How to Simulate Unpivot in Access
Cannot Log into SQL Server in Mssql-Server-Linux Container
SQL Update Set One Column to Be Equal to a Value in a Related Table Referenced by a Different Column