0

I want to transpose an SQL table from a row into a column of results. The statement will only return one record however at the time of running the query I will not know the names of attributes in the table. All the query will know is the table and the ID column to return the relevant record.

i.e. I would like to return this as a column of results: 

SELECT * FROM ExampleTable WHERE (PKCol = 'XYZ');

That is the only information I will know at the time of running the query in SQL Server 2012.

Thanks

NickyvV
  • 1,720
  • 2
  • 16
  • 18
TheIdiot
  • 307
  • 1
  • 5
  • 12
  • try to search for dynamic pivot – AK47 May 07 '14 at 11:15
  • @AK47 I have looked but they all seem to know the name of attributes before running the pivot? Thanks. – TheIdiot May 07 '14 at 11:20
  • So you are saying than, you don't know number of columns in your table before PIVOT.correct? – AK47 May 07 '14 at 11:22
  • @AK47 All I will know is the information in the above query: Table and PK Column. – TheIdiot May 07 '14 at 11:28
  • I think this will help you, http://stackoverflow.com/questions/21325394/dynamic-pivot-table-with-multiple-columns-in-sql-server – AK47 May 07 '14 at 11:30
  • @TheIdiot See this question http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server-2008 – Taryn May 07 '14 at 11:35
  • There is always more to a pivot question, and that is, how are you displaying this? in SSMS? In a report? in a form? in a data extract? – Nick.Mc May 07 '14 at 12:09

1 Answers1

0

You should retrieve column names from sys.columns system view, concat them in cusror and use UNPIVOT.

Something like this:

    DECLARE @columns AS NVARCHAR(MAX) = '', @columns_char AS NVARCHAR(MAX) = '', @query AS NVARCHAR(MAX)

    SELECT @columns += ',' + c.name, @columns_char += ',CAST(' + c.name + ' AS VARCHAR(255)) AS ' + c.name   FROM sys.columns AS c WHERE c.object_id = OBJECT_ID(N'Your Table Name')
    SELECT @columns = STUFF(@columns, 1, 1, ''), @columns_char = STUFF(@columns_char, 1, 1, '')
    SELECT @columns, @columns_char  

    SET @query = 
    N'SELECT 
        column_name,
        col
    FROM
    (
        SELECT ' + @columns_char + ' FROM ' + Your_table_name + N' AS t
        WHERE t.id = ' + Your Id + N'
) AS sel
    UNPIVOT
    (
        col FOR column_name IN(' + @columns + ')
    ) AS upt';

    EXEC sp_executesql @query
Alexander Kulakov
  • 11
  • 1