locked
Dynamically define column name RRS feed

  • 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
  • If you are using SSRS, Kalman Toth tells me that SSRS has dynamic pivot built-in.

    If the number of columns are fixed, you can create a temp table with generic names, and then use sp_rename:

    EXEC tempdb..sp_rename '#temp.col1', 'Name', 'COLUMN'


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    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/

    Regards !


    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 blog

    Thursday, December 27, 2012 6:52 PM
  • Thats perfect!!

    Thank you!

    Thursday, December 27, 2012 6:57 PM