Dynamically column names from select sql

Question

I have the following data output from my database

Observation             1   aug -2015
Improvement suggestion  1   dec -2015
Observation             1   dec -2015
Accident                2   jan -2016
Non Conformity          5   jan -2016
Observation             5   jan -2016

I've tried to figure out how to use PIVOT-table to make this work but cannot make it work. The date is dynamic depending on a date in the database. The output I am looking for is like below. Can someone please point me into right direction?

Look at the fiddle what I've tried so far I know it is using SUM right now, and that is not correct, but I cannot figure out what to use. http://sqlfiddle.com/#!3/0bd0c/4

PS. The data and the image are not related, so don't be fooled by the image. It is just an example

your tags should include your specific version of SQL Server — iDevlop, Jan 25 '16 at 09:54
[This thread](http://stackoverflow.com/q/10404348/243373) on StackOverflow has plenty of examples. Start from there. — TT., Jan 25 '16 at 10:00

score 17 · Accepted Answer · answered Jan 25 '16 at 10:01

17

Is this what you were looking for:

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

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ColumnName) 
    from tempData
    group by ColumnName, name
FOR XML PATH(''), Type
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query = N'SELECT Name, ' + @cols + N' from 
 (
    select Name, value, ColumnName
    from tempData
) x
pivot 
(
    SUM(value)
    for ColumnName in (' + @cols + N')
) p '

exec sp_executesql @query;

Changing this in your fiddle return the rows as you need it.

answered Jan 25 '16 at 10:01

Mihail Stancescu

4,088
1
16
21

All you did was to add Name to the query and removed type? That did the trick fore sure! – Pochen Jan 25 '16 at 10:07
You were faster than me :-) +1 from my side – Shnugo Jan 25 '16 at 10:09
Yeah @Pochen that indeed did it. And Thanks Shnugo :) – Mihail Stancescu Jan 25 '16 at 11:03

score 7 · Answer 2 · answered Jan 25 '16 at 10:07

Try it like this:

DECLARE @cols AS NVARCHAR(MAX)=
STUFF(
(
    SELECT DISTINCT ',[' + ColumnName + ']'
    FROM tempData
    FOR XML PATH('')
)
,1,1,'');

DECLARE @SqlCmd VARCHAR(MAX)=
'SELECT p.*
 FROM
 (
    SELECT *
    FROM tempData
 ) AS tbl
 PIVOT
 (
    SUM(Value) FOR ColumnName IN(' +  @cols +')
 ) AS p';

 EXEC(@SqlCmd);

Dynamically column names from select sql

2 Answers2

Linked

Related