Crosstab Queries in SQL Server

[RogerCooper]

I need to run a crosstab query directly in SQL Server (the query is being executed through a website). In Access this is easy enough

TRANSFORM Sum(dbo_HISTORY.NET_SALES) AS SumOfNET_SALES
SELECT dbo_HISTORY.CUSTOMER_NAME
FROM dbo_HISTORY
GROUP BY dbo_HISTORY.CUSTOMER_NAME
PIVOT dbo_HISTORY.YEAR;

In SQL Server I can do this

SELECT CUSTOMER_NAME, [2017], [2018],[2019],[2020], [2021], [2022]
FROM   
(SELECT CUSTOMER_NAME, YEAR, NET_SALES
FROM [SPEC_MIS].DBO.HISTORY) p 
PIVOT 
( 
SUM (NET_SALES) 
FOR YEAR IN 
( [2017], [2018], [2019], [2020], [2021], [2022] ) 
) AS pvt 
ORDER BY pvt.CUSTOMER_NAME;

But I want to determine the column headings from the data, rather than my having to change the query every year. How can I do this in SQL Server?

 

[Isaac]

One thing I have done with some success is to:

1) determine the distinct [Year values, in your case] values present and built them up to a literal string of '[' + value + ']' (etc etc, use String_Agg)
2) tie it all together using dynamic SQL.

I don't have an example right now as I've only done this at my current job and can't copy/paste between my virtual screen and this one unfortunately for security reasons and can't find the snippet for even a screenshot, but you can hopefully see how it can work

 

[Galaxiom]

Dynamic SQL is required. I have built very complex transform queries using the technique Isaac describes.

The key to it is to use a query to write the SQL. Here is a start to give you the idea.

DECLARE @SQL nvarchar(max)

@SQL = 'SELECT CUSTOMER_NAME, '

SELECT DISTINCT 
      @SQL = @SQL + '[' + CONVERT(varchar(4), [YEAR]) + '] ' + ', '
FROM 
      [SPEC_MIS].DBO.HISTORY)
ORDER BY
      [YEAR]

@SQL = LEFT(@SQL, LEN(@SQL)-1) + ' FROM '

etc

Since you are going to use the list of years again in the query, you could build that string and reuse it.

 

[arnelgp]

source:
Pivot dynamic column names – SQLServerCentral Forums

DECLARE @col VARCHAR(1000)

DECLARE @sql VARCHAR(2000)

SELECT @col = COALESCE(@col + ', ','') + QUOTENAME(YEAR) from HISTORY Group by YEAR

Set @sql='select * from (select CUSTOMER_NAME, YEAR, NET_SALES from HISTORY ) src PIVOT (sum(NET_SALES) FOR YEAR IN ('+@col+')) pvt'

EXEC(@sql)

 

[Galaxiom]

EXEC(@sql)

BTW When executing dynamic sql on a query that will be run repeatedly, consider using the system procedure sp_executesql instead. The difference is that the system procedure will save the query plan.

Even though it is dynamic sql, the query optimiser can recognise the structure of a similar query.

Not going to make a noticeable difference in this case but can be very significant for a complex query on a busy server.

 

[RogerCooper]

Thank you. I will give this a try.