CODEWITHSUNDEEP
sqlsql-serversql-server-2008
kalyanasundaram v
kalyanasundaram v

Reputation: 111

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

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

Upvotes: 3

Views: 3530

Answers (2)

randiel
randiel

Reputation: 290

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,

Upvotes: 0

user2065377
user2065377

Reputation: 458

-- 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)

Upvotes: 1

Related Questions