December 21, 2010 at 1:14 pm
A colleague asked the following:
How could I create a pivot that would show the names of all the tables
in my database as column headers, and the names of the columns in that
table below them? Here’s a mockup of what I’d want this to look like:
Table1 Table2 Table3 ...
Col-1a Col-2b Col-3a
Col-1b Col-2c Col-3e
Col-1c Col-3f
Col-1d Col-3g
Col-3h
I solved this with a brute-force and fairly ugly approach. I'm certain there's some elegant way to do it, but it escapes me. Here's what I did - can anyone tell me how I should have done it? Note that there are no requirements for performance, memory usage, etc. because this is executed ad-hoc once a quarter at most. Also, I used temporary tables to stage data so it was easier for the colleage to understand - I'm not asking if using temporary tables was the right idea, only if there's some much better approach to the problem.
DECLARE @TblCount int, @ColCount int, @SqlPart varchar(max), @Sql varchar(max)
SELECT tbl1.name AS TName,
cols1.name AS CName,
row_number () over (partition by tbl1.name order by tbl1.name, cols1.Column_ID) ColNum
INTO #TCList
FROM sys.tables tbl1
INNER JOIN sys.columns cols1 on tbl1.object_id= cols1.object_id
SELECT @ColCount = MAX(ColNum)
FROM #TCList
;
WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT Col.n AS ColNum
INTO #Columns
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ) Col ( n )
WHERE Col.n <= @ColCount
SELECT DISTINCT TName
INTO #Tables
FROM #TCList
SELECT @SqlPart = 'ISNULL((SELECT CName FROM #TCList t WHERE TName = ''$$TBL'' AND t.ColNum = c.ColNum), '''') AS [$$TBL]'
SELECT @Sql = COALESCE(@Sql + ',' + char(13) + char(10), 'SELECT ') + REPLACE(@SqlPart, '$$TBL', TName)
FROM #Tables
SELECT @Sql = @Sql + char(13) + char(10) + 'FROM #Columns c'
EXEC (@Sql)
DROP TABLE #TCList
DROP TABLE #Columns
DROP TABLE #Tables
Note that I may have been able to use sys.columns.column_id instead of row_numbering the results, but I wasn't sure SQL maintained that as an orderly number if columns are added / removed over time...
December 22, 2010 at 5:44 am
This was removed by the editor as SPAM
December 22, 2010 at 7:05 am
stewartc-708166 (12/22/2010)
You could have used sys.syscolumns.colorder, which maintains the ordinal position of the columns per table.
How can you be sure? My copy of Books Online says sys.syscolumns is a view provided for backward-compatibility (with SQL Server 2000) only, and the colorder column is described as "Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.". For that reason, one might choose to reference INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION instead.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 22, 2010 at 7:18 am
jteeter (12/21/2010)
I solved this with a brute-force and fairly ugly approach. I'm certain there's some elegant way to do it, but it escapes me. Here's what I did - can anyone tell me how I should have done it? Note that there are no requirements for performance, memory usage, etc. because this is executed ad-hoc once a quarter at most.
Why choose to do this in T-SQL at all? If this is a report, wouldn't SQL Server be better used as a database than a presentation tool? What is the consumer of the data? Excel? SSRS?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 22, 2010 at 7:44 am
I have to agree with Paul that there are better tools to do this kind of dynamic pivoting. Anyway, here's an alternative
DECLARE @Query VARCHAR(MAX)
DECLARE @TableNames VARCHAR(MAX)
DECLARE @Comma CHAR(1)
SELECT
@TableNames = COALESCE(@TableNames, '') + COALESCE(@Comma, '') + '[' + TABLE_NAME + ']',
@Comma = ','
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'
ORDER BY
TABLE_NAME
SET @Query = '
WITH TableColumns AS
(
SELECT
T.TABLE_NAME, C.COLUMN_NAME, C.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLES T
JOIN
INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_NAME = T.TABLE_NAME
WHERE
TABLE_TYPE = ''BASE TABLE''
)
SELECT
ORDINAL_POSITION, ' + @TableNames + '
FROM
TableColumns TC
PIVOT
(
MAX(COLUMN_NAME) FOR TABLE_NAME IN (' + @TableNames + ')
) P'
PRINT @Query
EXEC (@Query)
December 22, 2010 at 8:45 am
Peter Brinkhaus (12/22/2010)
Anyway, here's an alternative...
Definitely counts as elegant in my book. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 22, 2010 at 9:46 am
Thanks Peter. I knew a simple solution was out there, I just couldn't visualize it how to pivot it appropriately. As to other tools, for a product I wouldn't select SQL, but I was trying to figure out how to approach the problem in SQL in case I needed something like this in the future.
December 22, 2010 at 9:53 am
jteeter (12/22/2010)
Thanks Peter. I knew a simple solution was out there, I just couldn't visualize it how to pivot it appropriately. As to other tools, for a product I wouldn't select SQL, but I was trying to figure out how to approach the problem in SQL in case I needed something like this in the future.
In that case, also check out Jeff Moden's article on dynamic pivots:
http://www.sqlservercentral.com/articles/65048/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply