convert rows to columns

Question

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

Vinay Singh · Answer

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

Midhun Tp · Answer

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/

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

convert rows to columns

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)