SQL moving columns to rows

Question

I need to output data as a result set from a stored procedure where the columns of my source data are represented as rows in the output. I should see a row for each column in my output that is in my source data and I should see a column in my output for every row in my source data. I just don't see how I can do this using Pivot or the other methods that I have looked at.

Once I have the data oriented the way I like it, I imagine I will query sys.columns to inject the source data column names as the first column of my output. I will also likely have to query the source data values in advance that will be used as the output column names. I imagine some magnificent dynamic SQL in my future but I can sort all that out myself if needed. I am mostly concerned with how to convert columns to rows. See below:

SELECT
     ID
    ,XID
    ,Amount
FROM  (
       SELECT 11 as ID, 301 as XID, 50001 as Amount UNION ALL
       SELECT 12 as ID, 302 as XID, 50002 as Amount UNION ALL
       SELECT 13 as ID, 303 as XID, 50003 as Amount UNION ALL
       SELECT 14 as ID, 304 as XID, 50004 as Amount
    ) T1
/*
The above query returns this:

ID  XID Amount
----------------
11  301 50001
12  302 50002
13  303 50003
14  304 50004


Instead I would like to see this:

Column  11     12     13     14
-----------------------------------
ID      11     12     13     14
XID     301    302    303    304
Amount  50001  50002  50003  50004

*/

Answer 1

Assuming you need Dynamic.

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(ID) From Yourtable Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select [Column],' + @SQL + '
From (
        Select B.*
         From  YourTable A
         Cross Apply (
                      Values (1,''ID''    ,ID,ID)
                            ,(2,''XID''   ,ID,XID)
                            ,(3,''Amount'',ID,Amount)
                     ) B ([Seq],[Column],[Item],[Value])
     ) A
 Pivot (sum(Value) For [Item] in (' + @SQL + ') ) p
 Order By [Seq]
 '
Exec(@SQL);

Returns

If you Don't Need Dynamic

Select [Column],[11],[12],[13],[14]
From (
        Select B.*
         From  YourTable A
         Cross Apply (
                      Values (1,'ID'    ,ID,ID)
                            ,(2,'XID'   ,ID,XID)
                            ,(3,'Amount',ID,Amount)
                     ) B ([Seq],[Column],[Item],[Value])
     ) A
 Pivot (sum(Value) For [Item] in ([11],[12],[13],[14]) ) p
 Order By [Seq]