Answered by:
Dynamically define column name

Question
-
Hi Guys,
I would like to define column name in SELECT statement dynamically. Please review the screenshot and advise.
Thank you very much in advance!!
Wednesday, December 26, 2012 8:50 PM
Answers
-
Try
USE tempdb CREATE TABLE ColNames ( ColumnId VARCHAR(20) ,pkg INT ,[Column] VARCHAR(30) ) INSERT INTO ColNames ( ColumnId ,pkg ,[Column] ) VALUES ( 'Column1' ,234 ,'Name' ) ,( 'Column2' ,234 ,'Address' ) ,( 'Column3' ,234 ,'Zip' ) ,( 'Column4' ,234 ,'phone' ) ,( 'Column5' ,234 ,'email' ) ,( 'Column1' ,111 ,'Name' ) ,( 'Column2' ,111 ,'contact' ) CREATE TABLE Data ( pkg INT ,col1 VARCHAR(100) ,col2 VARCHAR(100) ,col3 VARCHAR(100) ,col4 VARCHAR(100) ,col5 VARCHAR(100) ,col6 VARCHAR(100) ); INSERT INTO Data VALUES ( 234 ,'John' ,'324 Stom street' ,'05323' ,'4453453456' ,'abc@hotmail.com' ,NULL ) ,( 234 ,'Sam' ,'21 Brook street' ,'05333' ,'4453453456' ,'cdf@hotmail.com' ,NULL ) ,( 111 ,'Patrick' ,'4453453456' ,NULL ,NULL ,NULL ,NULL ); -- Actual query starts here DECLARE @pkg INT = 234 ,@SQL NVARCHAR(max); WITH Cols AS ( SELECT C.Column_Name FROM INFORMATION_SCHEMA.Columns C WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Data' AND COLUMN_NAME LIKE 'Col%' ) ,ColMaps AS ( SELECT C.Column_Name ,CN.[Column] FROM Cols C LEFT JOIN ColNames CN ON replace(C.Column_Name, 'Col', 'Column') = CN.ColumnId AND Cn.pkg = @pkg ) SELECT @SQL = ( SELECT ', ' + quotename(Column_Name) + ' AS ' + quotename(coalesce([Column], Column_Name)) FROM ColMaps ORDER BY COLUMN_NAME FOR XML PATH('') ) SET @SQL = 'SELECT pkg' + @SQL + ' FROM Data where pkg = @pkg' PRINT @SQL -- test EXECUTE sp_executesql @SQL ,N'@pkg int' ,@pkg;
For every expert, there is an equal and opposite expert. - Becker's Law
My blog- Marked as answer by Vick72 Thursday, December 27, 2012 6:57 PM
Thursday, December 27, 2012 5:48 PM
All replies
-
Hi Vick,
Turning values from rows into columns is pivoting. As you may know, SQL Server produce such results using SELECT...PIVOT but the pivot list cannot accept variable nor subquery. The only way is to use power of Dynamic TSQL:
http://www.kodyaz.com/articles/t-sql-dynamic-pivot-table-example-code.aspx
- Proposed as answer by Naomi N Wednesday, December 26, 2012 9:10 PM
Wednesday, December 26, 2012 8:56 PM -
Wednesday, December 26, 2012 9:07 PM
-
You can try this:
DECLARE @sqlCommand varchar(1000) SET @sqlCommand = 'SELECT col1 as ' + @columnName1 + ', col2 as ' + @columnName2 + ' FROM yourTable' EXEC (@sqlCommand)
http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
Sergio Sánchez Arias
Wednesday, December 26, 2012 9:09 PM -
Thanks for replying, but we dont know how many columns are there... so above query won't help that much...
Thursday, December 27, 2012 4:59 PM -
Try
USE tempdb CREATE TABLE ColNames ( ColumnId VARCHAR(20) ,pkg INT ,[Column] VARCHAR(30) ) INSERT INTO ColNames ( ColumnId ,pkg ,[Column] ) VALUES ( 'Column1' ,234 ,'Name' ) ,( 'Column2' ,234 ,'Address' ) ,( 'Column3' ,234 ,'Zip' ) ,( 'Column4' ,234 ,'phone' ) ,( 'Column5' ,234 ,'email' ) ,( 'Column1' ,111 ,'Name' ) ,( 'Column2' ,111 ,'contact' ) CREATE TABLE Data ( pkg INT ,col1 VARCHAR(100) ,col2 VARCHAR(100) ,col3 VARCHAR(100) ,col4 VARCHAR(100) ,col5 VARCHAR(100) ,col6 VARCHAR(100) ); INSERT INTO Data VALUES ( 234 ,'John' ,'324 Stom street' ,'05323' ,'4453453456' ,'abc@hotmail.com' ,NULL ) ,( 234 ,'Sam' ,'21 Brook street' ,'05333' ,'4453453456' ,'cdf@hotmail.com' ,NULL ) ,( 111 ,'Patrick' ,'4453453456' ,NULL ,NULL ,NULL ,NULL ); -- Actual query starts here DECLARE @pkg INT = 234 ,@SQL NVARCHAR(max); WITH Cols AS ( SELECT C.Column_Name FROM INFORMATION_SCHEMA.Columns C WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Data' AND COLUMN_NAME LIKE 'Col%' ) ,ColMaps AS ( SELECT C.Column_Name ,CN.[Column] FROM Cols C LEFT JOIN ColNames CN ON replace(C.Column_Name, 'Col', 'Column') = CN.ColumnId AND Cn.pkg = @pkg ) SELECT @SQL = ( SELECT ', ' + quotename(Column_Name) + ' AS ' + quotename(coalesce([Column], Column_Name)) FROM ColMaps ORDER BY COLUMN_NAME FOR XML PATH('') ) SET @SQL = 'SELECT pkg' + @SQL + ' FROM Data where pkg = @pkg' PRINT @SQL -- test EXECUTE sp_executesql @SQL ,N'@pkg int' ,@pkg;
For every expert, there is an equal and opposite expert. - Becker's Law
My blog- Marked as answer by Vick72 Thursday, December 27, 2012 6:57 PM
Thursday, December 27, 2012 5:48 PM -
Thank you very much Naomi!!
Is it possible to remove all NULL columns?
Thursday, December 27, 2012 6:49 PM -
Sure. Instead of using COALESCE([Column],[Column_Name]) just use the Column. Or use INNER JOIN instead of the LEFT JOIN in the second CTE.
For every expert, there is an equal and opposite expert. - Becker's Law
My blogThursday, December 27, 2012 6:52 PM -
Thats perfect!!
Thank you!
Thursday, December 27, 2012 6:57 PM