This is tricky.
Here's one way:
Add a dataset to your report to drive the parameter. Use this query, replacing with your table name:
SELECT c.name
FROM sys.columns c
INNER JOIN sys.tables t
ON c.object_id = t.object_id
AND t.name = 'myTableName'
Now, create a parameter called @Columns. Set its available values to name from that dataset. Allow multiple values.
Now, create another data set using this query, again replacing myTableName with yours.
DECLARE @sColumns NVARCHAR(MAX)
DECLARE @holder TABLE (colName SYSNAME, seq INT)
INSERT INTO @holder
SELECT value, ID
FROM dbo.splitterMkII(@Columns,',')
;WITH rCTE AS (
SELECT CAST('CAST('+colName+' AS NVARCHAR) AS '+colName AS NVARCHAR(MAX)) AS colName, seq
FROM @holder
WHERE seq = 1
UNION ALL
SELECT a.colName + ', CAST('+r.colName+' AS NVARCHAR) AS '+r.colName, r.seq
FROM rCTE a
INNER JOIN @holder r
ON a.seq + 1 = r.seq
)
SELECT @sColumns = colName
FROM rCTE
WHERE seq = (SELECT MAX(seq) FROM rCTE)
DECLARE @dSQL NVARCHAR(MAX)
SET @dSQL = 'SELECT x,y, row FROM (SELECT '+@sColumns+', ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row FROM myTableName) c UNPIVOT ( x for y IN ('+@Columns+') ) p'
EXEC sp_executeSQL @dSQL
This will do the hard work for you. You're parameter is inserted into it, and processed to set up for a matrix.
Create a matrix in the report. Put row in the left column, y in the header row, and x in the values.
Run and enjoy.
Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
Really enjoyed it? See my profile!
My Tech Net
Articles.
