Assigning Multiple Values to a Variable Using SELECT Statement and Making SQL Query Work Dynamically

Question

I have the following static query to make a pivot table in SQL server:

SELECT 
item,
[2018-05]=CEILING(SUM(CASE WHEN New_Date = '2018-05' THEN Qty Else NULL END)),
[2018-06]=CEILING(SUM(CASE WHEN New_Date = '2018-06' THEN Qty Else NULL END)),
[2018-07]=CEILING(SUM(CASE WHEN New_Date = '2018-07' THEN Qty Else NULL END)),
[2018-08]=CEILING(SUM(CASE WHEN New_Date = '2018-08' THEN Qty Else NULL END)),
[2018-09]=CEILING(SUM(CASE WHEN New_Date = '2018-09' THEN Qty Else NULL END)),
[2018-10]=CEILING(SUM(CASE WHEN New_Date = '2018-10' THEN Qty Else NULL END)),
[2018-11]=CEILING(SUM(CASE WHEN New_Date = '2018-11' THEN Qty Else NULL END)),
[2018-12]=CEILING(SUM(CASE WHEN New_Date = '2018-12' THEN Qty Else NULL END)),
[2019-01]=CEILING(SUM(CASE WHEN New_Date = '2019-01' THEN Qty Else NULL END)),
[2019-02]=CEILING(SUM(CASE WHEN New_Date = '2019-02' THEN Qty Else NULL END)),
[2019-03]=CEILING(SUM(CASE WHEN New_Date = '2019-03' THEN Qty Else NULL END)),
[2019-04]=CEILING(SUM(CASE WHEN New_Date = '2019-04' THEN Qty Else NULL END)),
[2019-05]=CEILING(SUM(CASE WHEN New_Date = '2019-05' THEN Qty Else NULL END)),
[2019-06]=CEILING(SUM(CASE WHEN New_Date = '2019-06' THEN Qty Else NULL END)),
[2019-07]=CEILING(SUM(CASE WHEN New_Date = '2019-07' THEN Qty Else NULL END)),
[2019-08]=CEILING(SUM(CASE WHEN New_Date = '2019-08' THEN Qty Else NULL END)),
[2019-09]=CEILING(SUM(CASE WHEN New_Date = '2019-09' THEN Qty Else NULL END)),
[2019-10]=CEILING(SUM(CASE WHEN New_Date = '2019-10' THEN Qty Else NULL END)),
[2019-11]=CEILING(SUM(CASE WHEN New_Date = '2019-11' THEN Qty Else NULL END)),
[2019-12]=CEILING(SUM(CASE WHEN New_Date = '2019-12' THEN Qty Else NULL END)),
[2020-01]=CEILING(SUM(CASE WHEN New_Date = '2020-01' THEN Qty Else NULL END)),
[2020-02]=CEILING(SUM(CASE WHEN New_Date = '2020-02' THEN Qty Else NULL END))
 FROM dbo.A
 GROUP BY item
 ORDER BY item;

However, as there are too many Year-Month columns, I'd like to make it dynamic uisng a variable similar to the following code and get all distinct year-month values from "New_Date" col and assign them to the variable "piv_col":

DECLARE @piv_col NVARCHAR (250)
SELECT DISTINCT  @piv_col= New_Date FROM dbo.A

However, the @piv_col only takes the last value of the col. Could you please help me how I can assign all distinct values to the variable and use that variable instead of year-month columns ([2018-05], [2018-06], etc.) to make the query work dynamically?

I appreciate all your help!

Tables in a database aren't Excel sheets. You can't have dynamic columns either in a table or query. You can use the [PIVOT](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15#basic-pivot-example) clause to clean up the query quite a bit but you still need to specify the individual values that will become columns — Panagiotis Kanavos, Mar 05 '20 at 15:14
Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Isaac, Mar 05 '20 at 15:15

Assigning Multiple Values to a Variable Using SELECT Statement and Making SQL Query Work Dynamically

0 Answers0