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
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
Dynamic TSQL Pivot without aggregate function
Sample data
create table ExternalPersonRelationTable
(
PersonId int,
SubjectCode int
);
insert into ExternalPersonRelationTable (PersonId, SubjectCode) values
(4187, 3),
(4187, 278),
(4429, 3),
(4429, 4),
(4463, 99),
(4464, 174),
(4464, 175);
Solution
Start with a (limited) static version of the pivot query as a reference.
select piv.SubjectCode as Code,
isnull(convert(bit, piv.[4187]), 0) as [4187],
isnull(convert(bit, piv.[4429]), 0) as [4429],
isnull(convert(bit, piv.[4463]), 0) as [4463],
isnull(convert(bit, piv.[4464]), 0) as [4464]
from ExternalPersonRelationTable epr
pivot (max(epr.PersonId) for epr.PersonId in ([4187],[4429],[4463],[4464])) piv;
Identify the dynamic parts and construct (and validate) those.
-- constuct lists
declare @fieldList nvarchar(1000);
declare @pivotList nvarchar(1000);
with cte as
(
select epr.PersonId
from ExternalPersonRelationTable epr
group by epr.PersonId
)
select @fieldList = string_agg('isnull(convert(bit, piv.['
+ convert(nvarchar(10), cte.PersonId)
+ ']), 0) as ['
+ convert(nvarchar(10), cte.PersonId)
+ ']', ', ') within group (order by cte.PersonId),
@pivotList = string_agg('['
+ convert(nvarchar(10), cte.PersonId)
+ ']', ',') within group (order by cte.PersonId)
from cte;
-- validate lists
select @fieldList as FieldList;
select @pivotList as PivotList;
Merge the dynamic parts in the final query (and validate during development phase).
-- construct query
declare @query nvarchar(3000) = 'select piv.SubjectCode as Code, '
+ @fieldList
+ 'from ExternalPersonRelationTable epr '
+ 'pivot (max(epr.PersonId) for epr.PersonId in ('
+ @pivotList
+ ')) piv;';
-- validate query
select @query as Query;
Run the dynamic query.
-- run query
exec sp_executesql @query;
Result
Code 4187 4429 4463 4464
---- ----- ----- ----- -----
3 True True False False
4 False True False False
99 False False True False
174 False False False True
175 False False False True
278 True False False False
Fiddle to see things in action.
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;
Dynamic SQL Pivot Causing Duplicate Columns
select * is including the columns you are pivoting; try this:
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(g.Name)
FROM Geometries g
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query =
'SELECT [RunId], [RunDataId], [Run #], [Test #], Description, data1, data2, data3, ' + @cols + '
FROM (
SELECT
r.Id as [RunId], rd.Id as [RunDataId], r.RunNumber as [Run #], r.TestNumber as [Test #], r.Description,
rd.data1, rd.data2, rd.data3,
g.Name[Geometry], g.Value
FROM dbo.Runs r
INNER JOIN RunDatas rd ON r.Id = rd.RunId
INNER JOIN RunGeometries rg ON rg.RunId = r.Id
INNER JOIN Geometries g ON g.Id = rg.GeometryId
) as data
PIVOT
(
Max(Value) FOR Geometry IN (' + @cols + ')
) as p'
print @query -- you can also take a look at the code you are generating to help troublshoot
execute sp_executesql @query
You can also check the code that is being generated with print @query or select @query.
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
Sum of Grouped Count in SQL Query
Copy Data from One Column to Other Column (Which Is in a Different Table)
SQL Populate Table with Random Data
SQL 2005 - the Column Was Specified Multiple Times
How to Generate Ranks in MySQL
Prepared Statement on Postgresql in Rails
T-SQL Calculate Moving Average
Scope_Identity VS Ident_Current
Rowset Does Not Support Scrolling Backward
Two Single-Column Indexes VS One Two-Column Index in MySQL
Where Clause to Find All Records in a Specific Month
Database in Use Error with Entity Framework 4 Code First
Update Rows in One Table with Data from Another Table Based on One Column in Each Being Equal
Sorting Tree with a Materialized Path
Force Oracle to Return Top N Rows with Skip Locked