robert.sterbal 56890 (7/7/2015)
Is there a simple way to get a single row table with variable numbers of columns to output with 2 columns?i.e.
colA colB colC
1 5 23
colA 1
colB 2
colC 3
and handle an extra column easily:
colA colB colC colD
1 5 23 743
colA 1
colB 2
colC 3
colD 743
You will need to create a dynamic pivot. It will look something like the psuedocode below.
If you provide actual DML/DATA I can provide a working script.
(See this article on how to provide sample data: http://www.sqlservercentral.com/articles/Best+Practices/61537/)
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)
SELECT @Columns = -- query to get your column names
SET @Query = '
SELECT *
FROM SomeTable
PIVOT
(
SUM(Value)
FOR ColumnName IN
('
+ @Columns +
')
) AS Pivot'
EXEC SP_EXECUTESQL @Query