I am trying to generate a pivot table with dynamic column names, but failing miserably.
My table has the following structure:
id, int() PKEY
prod_no, VARCHAR(20)
f_month, INT
f_year, INT
f_value, INT
with sample data looking like this
-------------------
AB1234|1|2016|15698
-------------------
AB1234|2|2016|25438
-------------------
AB1234|3|2016|53323
-------------------
AB1234|1|2017|34535
-------------------
AB1234|2|2017|66244
-------------------
AB1234|3|2017|54534
-------------------
CD9876|1|2016|43278
-------------------
CD9876|2|2016|11245
-------------------
CD9876|3|2016|82432
-------------------
CD9876|1|2017|93563
-------------------
CD9876|2|2017|89356
-------------------
CD9876|3|2017|45724
-------------------
the result I'm after is something like this:
prod_no|1-2016|2-2016|3-2016|1-2017|2-2017|3-2017|
--------------------------------------------------
AB1234 |15698 |25438 |53323 |34535 |66244 |54534 |
--------------------------------------------------
CD9876 |43278 |11245 |82432 |93563 |89356 |45724 |
So the columns as prod_no, followed by dynamic columns being concatenation of f_month-f_year an the data as product number and value corresponding to the month-year in that column.
I played around with some dynamic pivot examples from the web but so far no luck with getting this to work
Try this:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar,c.f_month)+'-'+convert(varchar,c.f_year))
FROM dynpi c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
print @cols
set @query = 'SELECT prod_no,' + @cols + ' from
(
select prod_no, f_value,convert(varchar,f_month)+''-''+convert(varchar,f_year) as dyn
from dynpi
) x
pivot
(
max(f_value)
for dyn in (' + @cols + ')
) p '
execute(@query)
The result is:
*---------*---------*-------*-------*-------*-------*------*
|prod_no |1-2016 |1-2017 |2-2016 |2-2017 |3-2016 |3-2017|
*---------*---------*-------*-------*-------*-------*------*
|AB1234 |15698 |34535 |25438 |66244 |53323 |54534 |
*---------*---------*-------*-------*-------*-------*------*
|CD9876 |43278 |93563 |11245 |89356 |82432 |45724 |
*---------*---------*-------*-------*-------*-------*------*