How to show the vertical data horizentally through sql

my table has two column columnname and data

i issue a simple sql like select * from mytable then data is showing like

colname               data
-------------------   -----------
JID                   41185
WID                   0
AccountReference      LH169
OEReference           Ari002
InvoiceNumber         0

but i want to display data in different way like

JID     WID    AccountReference 开发者_如何学Go  OEReference     InvoiceNumber
41185   0      LH169              Ari002          0

if i need to show data horizentally then what sql i need to issue..........please help.

SQL isn't really about display. The problem you have is that you'd really need 2 queries (1 for colname and 1 for data) with no guarantee the data would be returned in the same order for each query. You really need to wrap some external code around this - save the query results in a 2-d array of string (or a collection etc) then iterate through each

SELECT  JID,WID,AccountReference,OEReference,InvoiceNumber
FROM    
( 
    SELECT colname, data FROM YourTableName
) 
p 
PIVOT 
( 
    Max(data) FOR colname 
    IN ([JID],[WID],[AccountReference],[OEReference],[InvoiceNumber])
) AS pvt

you can try below links. contains tutorials for the usage of Pivot.

Link1

Link2

If the values of colname are known in advance & unique;

SELECT * FROM tbl
PIVOT (
    MAX(data) 
    FOR colname in ([JID],[WID],[AccountReference],[OEReference],[InvoiceNumber])
) pv

You can find this in my blog: http://sql-tricks.blogspot.com/2011/04/sql-server-rows-transpose.html

You should change @xml variable like this:

SET @xml = ( SELECT colname,data,
                Row_Number() OVER ( ORDER BY ( SELECT   1
                                             ) ) Rn
         FROM   mytable
       FOR
         XML PATH('Row') ,
             ROOT('Root') ,
             ELEMENTS XSINIL
       ) ;