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
dynamic pivot with parameter passed in
exec sp_executesql @Sql, N' @WeekStart Date, @WeekEnd Date', @WeekStart = @WeekStart, @WeekEnd = @WeekEnd
Dynamic Pivot using Temp tables
To get each Dx_Code of a Patient_Account in the same line, you need to GROUP BY Patient_Account
, and use some aggregate function (like MIN() or MAX()) to get the Dx_Code and Dx_Desctiption.
Another way to make a dynamic query:
SELECT STRING_AGG(query_piece, '')
FROM (
(SELECT 'SELECT Patient_Account,' AS query_piece)
UNION ALL
(SELECT
CONCAT('MIN(CASE WHEN Dx_Rank = ''', Dx_Rank, ''' THEN Dx_Code END) AS ', Dx_Rank, ', ',
'MIN(CASE WHEN Dx_Rank = ''', Dx_Rank, ''' THEN Dx_Desctiption END) AS ', Dx_Rank, '_Description, ')
FROM Diagnosis
GROUP BY Dx_Rank)
UNION ALL
(SELECT 'Visit_Key
FROM Diagnosis
GROUP BY Patient_Account, Visit_Key;')) AS dynamic_query;
The result of the dynamic query is the query you have to execute to get the desired result:
SELECT Patient_Account,MIN(CASE WHEN Dx_Rank = 'Dx_1' THEN Dx_Code END) AS Dx_1, MIN(CASE WHEN Dx_Rank = 'Dx_1' THEN Dx_Desctiptio END) AS Dx_1_Description, MIN(CASE WHEN Dx_Rank = 'Dx_2' THEN Dx_Code END) AS Dx_2, MIN(CASE WHEN Dx_Rank = 'Dx_2' THEN Dx_Desctiptio END) AS Dx_2_Description, MIN(CASE WHEN Dx_Rank = 'Dx_3' THEN Dx_Code END) AS Dx_3, MIN(CASE WHEN Dx_Rank = 'Dx_3' THEN Dx_Desctiptio END) AS Dx_3_Description, Visit_Key
FROM Diagnosis
GROUP BY Patient_Account, Visit_Key;
Output with the example data in your question:
Patient_Account | Dx_1 | Dx_1_Description | Dx_2 | Dx_2_Description | Dx_3 | Dx_3_Description | Visit_Key |
---|---|---|---|---|---|---|---|
123456789 | J20 | Left Hip is Broken | A32 | Left Knee is broken | R4786 | Left Ankle is broken | #PAS203234 |
987654321 | DF346 | Right Arm is broken | DT342.12 | Right Wrist is broken | #PAS435678 |
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 |
T-SQL Dynamic pivot query and SQL Injection protection
There are a few issues I can see:
- You should use
QUOTENAME
on the pivotCASE
expression also. Use''''
as the second parameter for this, to get''
as the delimiter FOR XML
should use.value
to unescape XML characters- The third parameter of
STUFF
is the length. To ensure it is always correct, use a@separator
variable - Dynamic SQL should be in
nvarchar(max)
- The performance of
DISTINCT
over a calculate field may be slow, it may be wiser to group first in a derived table/subquery
DECLARE @separator nvarchar(10) = ',';
DECLARE @columnList nvarchar(max) = STUFF(
(SELECT @separator + 'MIN(CASE DATA_KEY WHEN ' + QUOTENAME(DATA_KEY, '''') + ' THEN DATA_VAL END) AS ' + QUOTENAME(DATA_KEY)
FROM (SELECT DISTINCT DATA_KEY FROM PARSED_DATA) pd
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 1, LEN(@separator), '');
You say you are using this to generate an INSERT
statement, so you should also check the column names against sys.columns
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 ║
╚═════════════════════════╩══════════╩════════════════════════════╩═════════╩══════════╩══════════════╩══════════════════════╝
Related Topics
Foreign Key Constraint May Cause Cycles or Multiple Cascade Paths
Managing and Debugging SQL Queries in Ms Access
What Is the Major Difference Between Varchar2 and Char
SQL Left Join VS Multiple Tables on from Line
Difference Between Single and Double Quotes in Sql
MySQL: Alternatives to Order by Rand()
Is the SQL Where Clause Short-Circuit Evaluated
Cross Join VS Inner Join in Sql
Oracle Sql: Update a Table With Data from Another Table
Only Inserting a Row If It's Not Already There
How to Query Between Two Dates Using MySQL
SQL Server 2008 Management Studio Not Checking the Syntax of My Query
Difference Between Scope_Identity(), Identity(), @@Identity, and Ident_Current()
Null Values Inside Not in Clause
How to Select Rows With Max(Column Value), Partition by Another Column in MySQL
Update Multiple Rows With Different Values in One Query in MySQL