sql select query convert 1st column as header and second column as row?

Question

Is it possible to convert 1st column as header and second column as row? Please find a example below

   Type  | qty  (Header)
  Apple    4
  Orange   10
  Grapes   5


Apple | Orange | Grapes (Header)
4       10         5

Is it MySQL or SQL Server? In SQL Server you need to look at PIVOT. — NYCdotNet, Sep 20 '13 at 17:36
http://stackoverflow.com/questions/24470/sql-server-pivot-examples — NYCdotNet, Sep 20 '13 at 17:42
You can do this, but **you must know at the outset how many columns the results will have** — Joel Coehoorn, Sep 25 '13 at 19:57

user2065377 · Accepted Answer · 2013-09-25T19:53:11.780

-- just some sample to work with

create table #fruits ([type] varchar(10), quantity int)
insert into #fruits select  'Apple',4 union all select 'Orange', 10 union all select 'Grapes', 5

--first get the Set of unique [result]s

declare @sqlStr varchar(max)

select @sqlStr = '['+ 
replace(
stuff((select distinct ','+[type] as [text()]
from #fruits s
    for xml path ('')) ,1,1,'')
    ,',','],[') + ']' 
from #fruits s1

select @sqlStr

--assemble dynamic query

declare @sqlStr1 varchar(max)
set @sqlStr1 = 'select *
from #fruits  a
pivot (sum (quantity) for [type] in ('+@sqlStr+')
    ) p'

-- run dynamic query

 exec (@sqlStr1)

hope this helps

ADDITION FOR CREATING A PERMANENT TABLE

declare @sqlStr1 varchar(max)
set @sqlStr1 = 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[TblFruits]'') AND type in (N''U''))
DROP TABLE [dbo].[TblFruits]

select *
into TblFruits
from #fruits a
pivot (sum (quantity) for [type] in ('+@sqlStr+')
        ) p'

-- run dynamic query
 exec (@sqlStr1)

how can I get the result in a temp table? set @sqlStr1 = 'select * into #temptable from #fruits a pivot (sum (quantity) for [type] in ('+@sqlStr+') ) p'. #temptable is unavailable for access outside exec statement — kalyanasundaram v, Sep 25 '13 at 18:47
Hi there, I don't believe you can. If you simply want to join it to some other table you better off doing that join within dynamic query, but if it is more complicated, you would need to create permanent table on a fly. I'll add the code to above answer. Please remember, I might be wrong and somebody might suggest a better solution. Spin it as a separate question and see what comes out of it. — user2065377, Sep 25 '13 at 19:49

score 0 · Answer 2 · edited Jan 09 '14 at 17:01

In SQL Server, exist STUFF() and EXEC() functions to make the same dynamic query.

select @script = STUFF( SELECT qty + ' AS ' + type + ', ' from table_orig);

EXEC (@script);

In MySql, you can use EXECUTE:

SET @sqltxt = null;
SELECT GROUP_CONCAT(CONCAT( qty, ' AS ', type, ' ') ) INTO @sqltxt
FROM table_orig;

SET @sqltxt = concat('SELECT ', @sqltxt , ' FROM DUAL');

PREPARE stmt FROM @sqltxt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Good luck,

sql select query convert 1st column as header and second column as row?

2 Answers2