FOR XML PATH in Dynamic Pivot Table

The most useful functionality of this clause is to concatenate multiple columns into a single row. You may wonder when exactly do you need to use this in practice? I'll tell you in a bit. Have you ever used Pivot in your static queries? If yes, then in that situation you had to know beforehand what values you needed to use after IN clause. To clarify take a look at this static pivot query:

SELECT * FROM
(
SELECT YEAR(OrderDate) as OrderYear,
????????Category,
TotalOrder
 FROM OrderTAble 
) AS D
PIVOT (SUM(TotalOrder)
FOR OrderYear IN ([2020],[2021],[2022]))
as P         

In the query above we explicitly mentioned years(2020,2021,2022) to have those as columns of the output.

So what if two months later we want to have year 2023 order records as well? In that case do you really want to come back and add 2023 hard-coded in your query?

So let's go back to our main topic. You asked when do you need to use FOR XML PATH in practice? Here is your answer: To write Dynamic Pivot technique. One of the most efficient yet simplest ways to write Dynamic Pivot is to use FOR XML PATH clause.

For practice purposes I created a test table called OrderTable and I want to pivot this table to have Order Years as columns. It is exactly like the example above but instead of using static Pivot, we are going to use dynamic pivot. Here is the query for create a dynamic pivot table:

use tempdb;
drop table? if exists OrderTable;
create table OrderTable
(
? ? OrderDate datetime,
? ? Category Varchar(40),
? ? TotalOrder Int
)

insert into OrderTable values ('2022/01/12', 'A', 300)
insert into OrderTable values ('2020/01/21', 'B', 800)
insert into OrderTable values ('2021/01/21', 'C', 600)
insert into OrderTable values ('2023/01/21', 'D', 300)
insert into OrderTable values ('2023/01/21', 'E', 400)

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


SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Year(OrderDate))?
? ? ? ? ? ? FROM OrderTable c
? ? ? ? ? ? FOR XML PATH(''), TYPE
? ? ? ? ? ? ).value('.', 'NVARCHAR(MAX)')?
? ? ? ? ,1,1,'')

select @cols as 'Columns'


set @query = 'SELECT Category, ' + @cols + ' from?
? ? ? ? ? ? (
? ? ? ? ? ? ? ? select Year(OrderDate) as YearOrder
? ? ? ? ? ? ? ? ? ? , TotalOrder
? ? ? ? ? ? ? ? ? ? , category
? ? ? ? ? ? ? ? from OrderTable
? ? ? ? ? ?) x
? ? ? ? ? ? pivot?
? ? ? ? ? ? (
? ? ? ? ? ? ? ? ?sum(TotalOrder)
? ? ? ? ? ? ? ? for YearOrder in (' + @cols + ')
? ? ? ? ? ? ) p '

execute(@query)

drop table OrderTable        

The output is shown below:

In this example as query shows we used various functions like STUFF and QUOTENAME alongside FOR XML PATH to achieve the first result (Columns). With the use of FOR XML PATH we were able to concatenate all years into a single row so that we could feed it properly to the IN clause of Pivot. Now if years go by we do not need to worry about adding them to our pivot query.

Thank you for reading. I hope this article was helpful for you.