SQL Server - Convert columns to rows

I have a table on which simple select gives out put like below

I want to write a select statement to output like below

Can someone help me...

Answers


Since you are basically rotating your current columns of Sale, Income and Profit into rows and then move the month values to columns, then you will want to first unpivot the current columns, then pivot the months.

Depending on your version of SQL Server there are a few ways that you can unpivot the data. You can use the UNPIVOT function or CROSS APPLY:

select month, type, value
from yourtable
cross apply
(
  select 'Sale', sale union all
  select 'Income', Income union all
  select 'Profit', Profit
) c (type, value)

See SQL Fiddle with Demo. This will convert your current data into:

| MONTH |   TYPE | VALUE |
|-------|--------|-------|
|   Jan |   Sale |   100 |
|   Jan | Income |    50 |
|   Jan | Profit |    10 |
|   Feb |   Sale |    20 |
|   Feb | Income |    40 |

Then you can use the PIVOT function to convert the months into your column headers.

select type, Jan, Feb, Mar, Apr
from
(
  select month, type, value
  from yourtable
  cross apply
  (
    select 'Sale', sale union all
    select 'Income', Income union all
    select 'Profit', Profit
  ) c (type, value)
) d
pivot
(
  sum(value)
  for month in (Jan, Feb, Mar, Apr)
) piv;

See SQL Fiddle with Demo.

if you have an unknown number of months, then you can use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct N',' + QUOTENAME(Month) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT type, ' + @cols + N' 
            from 
            (
                select month, type, value
                from yourtable
                cross apply
                (
                  select ''Sale'', sale union all
                  select ''Income'', Income union all
                  select ''Profit'', Profit
                ) c (type, value)
            ) x
            pivot 
            (
                sum(value)
                for month in (' + @cols + N')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo


You Can Use UNPIVOT then PIVOT THE BEST IS TO DO QUERY EMBEDED SQL create DISTINCT COLUMS of months with STUFF function then replace FOR oMonth IN ([January-2013], [February-2013], [March-2013], [April-2013]) here core query

 SELECT  
*
FROM
(  SELECT      
 oMonth, value,col
 from (
 select DATENAME(month,oDate)   + '-' + CAST(YEAR(  oDate) as varchar) as oMonth,        Sales ,Income,Profit
 FROM            SalesSource 
 )A
unpivot
 (
  value for col in ( Sales ,Income,Profit)
 ) u

 ) as sourceTable
 PIVOT
 (
 sum( value)  
 FOR oMonth IN ([January-2013], [February-2013], [March-2013], [April-2013])
 ) AS PivotTable;

Need Your Help

How do I "execute" only one key press inside a while loop? (PyWin32)

python python-3.x while-loop keypress pywin32

I have a paradox here. I'm using GetKeyState in order to retrieve the state of a giving vk_code. I've developed two functions to aid me in my task:

Actors are getting lost when drop failed -libgdx

java drag-and-drop libgdx

I am working on a rummikub game, I have a board which have tiles on it, and all positions on board are defined as TileActor. Some tile actors have actual tiles on them, some are empty. Problem is: ...