How to get rows data as column header in SQL Server 2005

Question

I am having a data of 8 rows with single column from the table as

PeriodName
-----------
1st-Period
2nd Period
3rd Period
4th Period
5th Period
6th Period
7th Period
8th Period

I want to display that 8 rows data as columns in temp table, could anyone please help me?

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — S3S, Nov 21 '16 at 22:11

score 0 · Answer 1 · answered Nov 29 '16 at 07:25

You can use dynamic SQL and execute it with sp_executesql command

First I concatenate column values in SQL Then call sp_executeSQL sp

Here is a sample

Declare @sql nvarchar(max)
SELECT @sql =
 'Select ' +
  STUFF(
    (
    SELECT
      ',' + QUOTENAME(PeriodName)
    FROM dbo.Periods
    FOR XML PATH(''),TYPE
    ).value('.','VARCHAR(MAX)'
    ), 1, 1, ''
  ) + ' From myTable' 

exec sp_executesql @sql

SQL QUOTENAME function is used to stay safe with column names including spaces and other special characters

How to get rows data as column header in SQL Server 2005

1 Answers1