Convert Rows into Columns... (Cross tab).

Hi genius,

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.

Thanks in advance

Regards,
j_jst

Hi,

I also have a same problem. Can you suggest what was your solution

regards

Josh

I also need to do something similar...

It was suggested that I would probably need to use a cursor in a stored procedure which loops through the rows and updates a temporary table with the values I need.

I havenĄŻt got round to doing this yetĄ­ so if anybody has a solution which I could have a look at IĄŻd be very grateful.

IĄŻll post my solution when IĄŻve got it.

Jon:

What version of SQL Server are you using Also, a cursor is NOT normally a good idea. Take a look at some of these posts:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1372104&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=892822&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=447559&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=437891&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=588762&SiteID=1

Please open a new thread and post what you are trying to accomplish.

select [Month],
max(case when [Status]='I' then [Count] end) as I,
max(case when [Status]='O' then [Count] end) as O,
max(case when [Status]='S' then [Count] end) as S
from mytable
group by [Month]
order by [Month]

Thanks for the links kent.

I have managed to solve my problem.