TSQL: Complete Column List
I want to use a pivot function to convert column row values to individual columns. There are over 100 different values in this column, and hardcoding each individual value in the "for" clause of the pivot function would be very time consuming and bad for maintainability purposes. I was wondering if there is an easier way to solve this problem?
thank
0
source to share
1 answer
You can use Dynamic SQL PIVOT
for this type of query. Dynamic SQL will get a list of the elements you want to transform on runtime, which avoids having to hard-code each element:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.condition_id)
FROM t c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT memid, ' + @cols + ' from
(
select MemId, Condition_id, condition_result
from t
) x
pivot
(
sum(condition_result)
for condition_id in (' + @cols + ')
) p '
execute(@query)
If you are posting sample data to be converted, I can customize my query to demonstrate.
+3
source to share