Convert row data into Column

Hi members

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks but this is not the required query. I know anout that but i require the row data will group as a column. I dont wana specify column name.

Please reply

|||If you're using SQL Server 2005 you can use the PIVOT command, but you still have to specify the columns to be listed in the IN clause.|||

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

|||

I am not getting the out put i require , Again you are using hardcoding stuff. I know the Data in table but , as i cant specify them..... Becuase they are also coming from different table. Please help me. i am stucked.....

The query sould be running on sql and oracle both

|||

What you then could do is to query for a table as the following

Month January February March (...)
=======================================
O 2 10 100
-
I 15 22 32
-
S 100 45 3
-

an afterwards pivoting it with the appropiate function mentioned. This works cause there won′t be more than 12 month as far as someone invents one :-)

But the thing that you have to both support Oracle and SQL Server for the query won′t work with this special functions, because although TSQL and PLSQL have some similar syntax components they differ in specialized ones.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Nope, this is not desire result, plz help me out.................|||I know that this is not the result as you need it, that is where the PIVOT command would take care of, but unless you don′t have any operator like that in Oracle (can′t remember if there is any operator which can do this) you can′t do crossuse this query.

-Jens Suessmeyer.|||help me out , by the result require , either for SQL or PL SQL.........|||

Don't know if you have gotten your answer as of yet, but if not, post some example tables, with create statements, some insert statements for the data, what your exact requirements are and perhaps your "dream" syntax that would make you happy (we'll try to match it) and the exact output.

No guarantees, but it will make sure we are all on the same page...

|||

Louis Davidson wrote:

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

This is the correct answer = Cross Tab Query

You want to use CASE WHEN ... THEN ... ELSE ... END AS []

Adamus

|||

I guess, the threader hate the hardcoding stuff like "I","S",or so, cause there might be other unposted status value.

Here I post one dynamic SQL aiming to resolve the uncertainty of status value.

Regards.

--create table
create table Raw_Table
(
RecID bigint identity(1,1) not null,
RecDate smalldatetime not null,
Status varchar(1) not null,
Constraint PK_Raw_Table primary key (RecID)
)
--insert data, Repeat following T-Sql with different 'Status' Value for several times
declare @.d datetime
set @.d='2006-07-10'
declare @.mtimes int
set @.mtimes=0
while(@.mtimes<453)
begin
insert into Raw_Table(RecDate,Status)
values(@.d,'K')
set @.mtimes=@.mtimes+1
end
--Aggregate Raw_Table
select datename(month,recdate) as [month],Status,count(*)
as [count]
into ##tempresult
from Raw_Table
group by datename(month,recdate),Status
order by datename(month,recdate)

--Dynamic SQL
declare @.Dsql nvarchar(4000)
declare @.VLastStatus varchar(1)
declare @.VNextStatus varchar(1)

--declare cursor
declare MyCurs Cursor for
select distinct Status from ##tempresult

open MyCurs
fetch next from MyCurs into @.VNextStatus
set @.VLastStatus=''
while @.@.Fetch_Status=0
begin
--this if makes the initial comb Result
if not exists (select * from tempdb.dbo.sysobjects where [name]='##TempResult_Comb_')
begin
set @.Dsql = 'select distinct month into ##TempResult_Comb_ from ##TempResult'
exec sp_executeSql @.Dsql
end
--this @.dsql makes each status table
set @.Dsql=
'select Month ,status,count into ##TempResult_'
+ @.VNextStatus
+' from ##tempresult where Status='
+''''+@.VNextStatus+''''
exec sp_executeSql @.Dsql

--this dsql combined each status table and last comb table
set @.Dsql= 'select a.*,b.count as '+@.VNextStatus+' into ##tempResult_Comb_'+
@.VNextStatus+' from ##TempResult_Comb_'+@.VLastStatus+' as a full join ##TempResult_'+@.VNextStatus+' as b
on a.month=b.month'
exec sp_executeSql @.Dsql

set @.VLastStatus=@.VNextStatus
fetch next from MyCurs into @.VNextStatus
end
close MyCurs
Deallocate MyCurs
--this @.Dsql Select Final Result
set @.Dsql='select * from ##tempResult_Comb_'+@.VLastStatus
exec sp_executeSql @.Dsql