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!!

Answer 1

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

Answer 2

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, [e-mail]

Answer 3

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

Answer 4

Thanks for replying, but we dont know how many columns are there... so above query won't help that much...

Answer 5

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'
	,'[e-mail]'
	,NULL
	)
	,(
	234
	,'Sam'
	,'21 Brook street'
	,'05333'
	,'4453453456'
	,'[e-mail]'
	,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

Answer 6

Thank you very much Naomi!!

Is it possible to remove all NULL columns?

Answer 7

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

Answer 8

Thats perfect!!

Thank you!