Loading

convert rows to columns

convert rows to columns

Mayur Gujrathi

Mayur Gujrathi

10y929SQL

this is my result set of sql query.

Header	Mobile	EXE	Web	OverallOnline
% of Revenue contribution	14.00	14.00	14.00	14.00
% of your clients logging in	14.00	14.00	14.00	14.00
% of your clients Traded	14.00	14.00	14.00	14.00
National avg % of Revenue contribution	14.00	14.00	14.00	14.00

How can i convert to

For	% of Revenue contribution	% of your clients logging in	% of your clients Traded	National avg % of Revenue contribution
Mobile
EXE
Web
OverallOnline

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vinay Singh10y ago
Hi again,

If you have an unknown number of columnnames that you want to transpose, then you can use dynamic SQL using PIVOT. Checkout this query

DECLARE @cols AS NVARCHAR(MAX),

@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)

from yourtable

group by ColumnName, id

order by id

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)')

,1,1,'')

set @query = 'SELECT ' + @cols + ' from

(

select value, ColumnName

from yourtable

) x

pivot

(

max(value)

for ColumnName in (' + @cols + ')

) p '

execute(@query)

See SQL Fiddle with Demo.

for detail check the link
http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server
0
Midhun Tp10y ago
Hi,
Please use pivot method in sql. Please go through below links -
http://www.c-sharpcorner.com/UploadFile/8911c4/pivot-examples-in-sql-server/
http://www.c-sharpcorner.com/uploadfile/f0b2ed/pivot-and-unpovit-in-sql-server/
https://www.simple-talk.com/sql/t-sql-programming/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask/
0