SQL Server dynamic PIVOT query?
Dynamic SQL PIVOT:
create table temp
(
date datetime,
category varchar(3),
amount money
)
insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)
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 date, ' + @cols + ' from
(
select date
, amount
, category
from temp
) x
pivot
(
max(amount)
for category in (' + @cols + ')
) p '
execute(@query)
drop table temp
Results:
Date ABC DEF GHI
2012-01-01 00:00:00.000 1000.00 NULL NULL
2012-02-01 00:00:00.000 NULL 500.00 800.00
2012-02-10 00:00:00.000 NULL 700.00 NULL
2012-03-01 00:00:00.000 1100.00 NULL NULL
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
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
SQL Query with Dynamic Columns Using Pivot
Using Dynamic Sql
IF OBJECT_ID('tempdb..#TempData', 'U') IS NOT NULL
DROP TABLE #TempData;
CREATE TABLE #TempData (
[Site] CHAR(3) NOT NULL,
Model VARCHAR(30) NOT NULL,
SomeCount INT NOT NULL DEFAULT(0)
);
INSERT #TempData (Site, Model, SomeCount) VALUES
('AAA', 'ProLiant DL380 G7', 1),
('AAA', 'OptiPlex 790', 500),
('BBB', 'OptiPlex 780', 80),
('CCC', 'OptiPlex 790', 23);
Declare @DynamicCol nvarchar(max),@DynamicColNull nvarchar(max)
,@Sql nvarchar(max)
SELECT @DynamicColNull=STUFF((SELECT DISTINCT ', '+'ISNULL('+QUOTENAME(Model),','+'''0'''+') As '+QUOTENAME(Model)
FROM #TempData FOR XML PATH ('')),1,2,'')
SELECT @DynamicCol=STUFF((SELECT DISTINCT ', '+QUOTENAME(Model) FROM #TempData FOR XML PATH ('')),1,2,'')
SET @Sql='SELECT [Site], '+@DynamicColNull+' From
(
SELECT * from #TempData
)
AS Src
PIVOT
(
MAX(SomeCount) FOR [Model] IN ('+@DynamicCol+')
)AS Pvt'
PRINT @Sql
EXEC(@Sql)
Result
Site OptiPlex 780 OptiPlex 790 ProLiant DL380 G7
AAA 0 500 1
BBB 80 0 0
CCC 0 23 0
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 SQL Rolling 12 Months Pivot
You simply need to add a WHERE
filter. This needs to be in both parts of the query.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF(
(SELECT ',' + QUOTENAME(DATENAME(mm, EOMONTH(Date)) + ' of ' + DATENAME(year, EOMONTH(Date))) AS months_ago
FROM [TimeEntryList]
WHERE [Date] > DATEADD(year, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
GROUP BY EOMONTH(Date)
ORDER BY EOMONTH(Date)
FOR XML PATH(''), TYPE
).value('text()[1]', 'NVARCHAR(MAX)')
,1,1,'');
SET @query = '
SELECT
CustName,
' + @cols + '
from (
select
[CustName],
datename(mm,[Date])+'' of ''+datename(year,[Date])AS months_ago, [Hours] AS
NetQty
from [TimeEntryList]
WHERE [Date] > DATEADD(year, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
) as source
pivot
(
sum(NetQty) For months_ago in (' + @cols + ')
) as PivotTable;
';
execute sp_executesql @query;
You could even pass through a starting date parameter, like this:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@dateFrom datetime;
SET @dateFrom = DATEADD(year, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1));
SET @cols = STUFF(
(SELECT ',' + QUOTENAME(DATENAME(mm,EOMONTH(Date)) + ' of ' + DATENAME(year,EOMONTH(Date)) AS months_ago
FROM [TimeEntryList]
WHERE [Date] > @dateFrom
GROUP BY EOMONTH(Date)
ORDER BY EOMONTH(Date)
FOR XML PATH(''), TYPE
).value('text()[1]', 'NVARCHAR(MAX)')
,1,1,'');
SET @query = '
SELECT
CustName,
' + @cols + '
from (
select
[CustName],
datename(mm,[Date])+'' of ''+datename(year,[Date])AS months_ago, [Hours] AS
NetQty
from [TimeEntryList]
WHERE [Date] > @dateFrom
) as source
pivot
(
sum(NetQty) For months_ago in (' + @cols + ')
) as PivotTable;
';
execute sp_executesql
@query
N'@dateFrom datetime',
@dateFrom = @dateFrom;
Dynamically pivot rows into columns - SQL Server
Here is one way to do it using Dynamic Pivot
DECLARE @sql VARCHAR(max)='',
@col_list VARCHAR(8000)=''
SET @col_list = (SELECT DISTINCT Quotename([question]) + ','
FROM Yourquery
FOR xml path(''))
SET @col_list = LEFT (@col_list, Len(@col_list) - 1)
SET @sql = 'select [DtCreated],[UserName]' + @col_list
+ ' from Yourquery pivot (max([Answer]) for [Question] in ('
+ @col_list + '))pv'
EXEC(@sql)
Update : You are missing Alias
name to the sub-select
SET @sql = 'select [DtCreated],[UserName]' + @col_list
+ ' from (SELECT
sf.[DtCreated],
sf.[UserName],
fc.Title,
sv.Value
FROM [form].[SubmissionForm] sf
inner join [form].[Submission] s on
sf.id = s.SubmissionForm_Id
inner join [form].[FormComponent] fc on s.FormComponentId = fc.Id
inner join [form].[SubmissionValue] sv on s.Id = sv.Submission_Id
) a --here
pivot (max([sv.Value]) for [fc.Title] in ('
+ @col_list + '))pv'
EXEC(@sql)
DEMO :
Schema setup
CREATE TABLE #Table1
([DtCreated] datetime, [UserName] varchar(8), [Question] varchar(11), [Answer] varchar(26))
;
INSERT INTO #Table1
([DtCreated], [UserName], [Question], [Answer])
VALUES
('2016-09-24 14:30:11', 'mauricio', 'Senha', '99658202'),
('2016-09-24 14:30:11', 'mauricio', 'Inteiro', '10'),
('2016-09-24 14:30:11', 'mauricio', 'Telefone', '(915) 438-05'),
('2016-09-24 14:30:11', 'mauricio', 'Email', 'mauriiciobarbosa@gmail.com'),
('2016-09-24 14:30:11', 'mauricio', 'Texto Livre', 'nksnksjksj nsjsnsjjs'),
('2016-09-24 14:30:11', 'mauricio', 'Decimal', '0.9')
;
Query :
declare @sql varchar(max)='',@col_list varchar(8000)=''
set @col_list = (select distinct quotename([Question])+',' from #Table1
for xml path(''))
set @col_list = left (@col_list,len(@col_list)-1)
set @sql = 'select [DtCreated],[UserName]'+@col_list+' from
#Table1
pivot (max([Answer]) for [Question] in ('+@col_list+'))pv'
exec(@sql)
Result :
╔═════════════════════════╦══════════╦════════════════════════════╦═════════╦══════════╦══════════════╦══════════════════════╗
║ DtCreated ║ Decimal ║ Email ║ Inteiro ║ Senha ║ Telefone ║ Texto Livre ║
╠═════════════════════════╬══════════╬════════════════════════════╬═════════╬══════════╬══════════════╬══════════════════════╣
║ 2016-09-24 14:30:11.000 ║ mauricio ║ mauriiciobarbosa@gmail.com ║ 10 ║ 99658202 ║ (915) 438-05 ║ nksnksjksj nsjsnsjjs ║
╚═════════════════════════╩══════════╩════════════════════════════╩═════════╩══════════╩══════════════╩══════════════════════╝
SQL Server Pivot Dynamic Sql with two columns
I think you will need multiple PIVOT's. It's made slightly more complicated by a few things
- Code/Label columns are not sequentially named
CodeX,CodeY,...
- Salary columns are sequentially named
Salary1,Salary2,...
- SELECT list columns must be grouped together
"CodeX,Salary1,CodeY,Salary2....*
SET @cols = STUFF((SELECT ',' + QUOTENAME(c.important) +','+ QUOTENAME( 'custom' + CAST(ROW_NUMBER() OVER (ORDER BY c.id) as VARCHAR))
You had the right idea there, building a concatenated list of both columns. But you'll actually need 3 variables: 1) For code columns 2) for salary columns and 3) for select list.
Note: Must use the same ORDER BY in all queries
DECLARE @LabelCols AS VARCHAR(MAX),
@SalaryCols AS VARCHAR(MAX),
@SelectCols AS VARCHAR(MAX),
@Query AS VARCHAR(MAX);
-- ** SQL Server 2012 doesn't support STRING_AGG()
SET @LabelCols = STUFF(( SELECT ',' + QUOTENAME(cd.label)
FROM code cd INNER JOIN client c ON c.fkcode = cd.id
ORDER BY cd.id
FOR XML PATH('')
),1,1,'')
SET @SalaryCols = STUFF(( SELECT ',' + QUOTENAME('Salary'+
CAST(ROW_NUMBER() OVER (ORDER BY cd.id) AS VARCHAR(20))
)
FROM code cd INNER JOIN client c ON c.fkcode = cd.id
FOR XML PATH('')
),1,1,'')
SET @SelectCols = STUFF(( SELECT ',' + QUOTENAME(cd.label)
+ ',' + QUOTENAME('Salary'+
CAST(ROW_NUMBER() OVER (ORDER BY cd.id) AS VARCHAR(20)))
FROM code cd INNER JOIN client c ON c.fkcode = cd.id
FOR XML PATH('')
),1,1,'')
Then use the three variable in your SELECT:
SET @Query = '
SELECT ClientId
, CodeId
, '+ @SelectCols +'
FROM
(
SELECT cd.id AS CodeId
, cd.label
, cd.labVal
, c.salary
, ''Salary''+ CAST(ROW_NUMBER() OVER (ORDER BY c.id) AS VARCHAR(20)) AS salaryLabel
FROM code cd
INNER JOIN client c ON c.fkcode = cd.id
) x
PIVOT
(
MAX(labVal)
FOR label IN (' + @LabelCols +')
) p1
PIVOT
(
MAX(salary)
FOR salaryLabel IN (' + @SalaryCols +')
) p2
'
EXECUTE (@Query)
Results:
CodeId | Important 1 | Salary1 | Important 2 | Salary2 | Important 3 | Salary3
-----: | :---------- | ------: | :---------- | ------: | :---------- | ------:
1 | Code1 | 120 | null | null | null | null
2 | null | null | Code2 | 1220 | null | null
3 | null | null | null | null | Code3 | 120
db<>fiddle here
dynamic pivot with parameter passed in
exec sp_executesql @Sql, N' @WeekStart Date, @WeekEnd Date', @WeekStart = @WeekStart, @WeekEnd = @WeekEnd
Related Topics
SQL How to Search a Many to Many Relationship
How to Use Output to Capture New and Old Id
Good Database and Structure to Store Synonyms
Hive - How to Further Optimize a Hiveql Query
Average Difference Between Two Dates, Grouped by a Third Field
Visual Studio 2013 SQL Query and View Designer Not Appearing
Call Function for Specifying Columns in SQL Transform Query
Rename a Column in All the Tables - SQL
Sql- Union All a Large Number of Tables
Xquery - How to Use the SQL:Variable in 'Value()' Function
Creating a SQL Table from a Xls (Excel) File
Rolling Sum Previous 3 Months SQL Server
Time Based Priority in Active Record Query
Convert Delimited String to Rows in Oracle
How to Compare If Two Strings Contain the Same Words in T-SQL for SQL Server 2008