How to display the all records of table as columns in sql query?

Question

I have a table with 200 records so i need to show all 200 rows as 200 columns it is not possible to write all 200 records in pivot IN() section as when i insert 201st record in table its column should also to be shown.

select * from Table_Name

Returns 200 rows

so i need to show 200 columns in other queries

SELECT * FROM
(
select RecordName from Table_Name
) AS T
PIVOT (Max(RecordName) FOR MiscMasterName IN (Record1,Record2)) AS T2

gives 2 columns like wise i need 200 columns such that if i add 201st records and wherever i execute the query i get 201 columns after inserting new record....

possible duplicate of [T-SQL Pivot? Possibility of creating table columns from row values](http://stackoverflow.com/questions/2922797/t-sql-pivot-possibility-of-creating-table-columns-from-row-values) — NickyvV, Feb 28 '14 at 07:33

score 0 · Accepted Answer · answered Feb 28 '14 at 07:42

You need to do a dynamic pivot. Something like this:

Test data:

CREATE TABLE Table_Name(MiscMasterName VARCHAR(200),RecordName VARCHAR(200))
INSERT INTO Table_Name
VALUES
    ('Record1','aValue'),
    ('Record2','SomeValue')

Finding the columns

DECLARE @cols VARCHAR(MAX)
SET @cols=STUFF
(
    (
        SELECT
            ',' +QUOTENAME(MiscMasterName)
        FROM
            Table_Name
    FOR XML PATH('')
    )
,1,1,'')

Declare and executing dynamic sql

DECLARE @query NVARCHAR(4000)=
N'SELECT 
* 
FROM
(
    SELECT 
        RecordName,
         MiscMasterName
    from 
        Table_Name
) AS T
PIVOT (Max(RecordName) FOR MiscMasterName IN ('+@cols+')) AS T2'
EXECUTE(@query)

Cleaning up after myself

DROP TABLE Table_Name

Output

Record1 Record2 
aValue  SomeValue

How to display the all records of table as columns in sql query?

1 Answers1