CODEWITHSUNDEEP
sqlvb.netwinformssql-server-cepivot
SarathSprakash
SarathSprakash

Reputation: 4624

How to convert row to columns?

I have the below table:

  PROCESS ID       VOUCHER DATE     CATEGORY_ID     PROJECT     AMOUNT
  -----------      ------------    ------------     -------     -------
  1001             12/03/13         Miscellaneous   pnr         1000
  1001             12/03/13         Miscellaneous   pjk         2000
  1002             20/07/13         Local Travel    pnr         3000
  1002             20/07/13         Local Travel    npk         3400
  1003             29/09/14         Miscellaneous   jpg         1000
  1004             23/10/13         Local Travel    pnr         2000
  1005             24/10/13         Miscellaneous   pnr         1000
  1005             24/10/13         Local Travel    pnr         1000

In the interface I will give VOUCHER DATE as between some date e.g., 20/01/13 and 27/10/13 and I have to get the output as in the format below:

  CATEGORY_ID       pnr         npk         jpg
  -----------      -----       -----       -----      
  Miscellaneous    1000         0           1000
  Local Travel     6000         3400        0

The PROJECT is dynamic, it can vary.

Upvotes: 0

Views: 194

Answers (2)

Hart CO
Hart CO

Reputation: 34774

Try this, it works in SQL Server 2008:

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

SELECT @cols = STUFF((SELECT ',' + PROJECT
                    FROM (SELECT DISTINCT PROJECT FROM Table1 )sub
                   FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


SET @query = 'SELECT * FROM
                (
                SELECT CATEGORY_ID, PROJECT, AMOUNT 
                FROM Table1   
                --WHERE VOUCHER_DATE BETWEEN '' AND ''
                ) AS T1
                PIVOT (SUM(AMOUNT) FOR PROJECT IN ('+@cols+')) AS T2

'
EXEC(@query)

SQL Fiddle

Basically, you use dynamic SQL to set the list of projects since it can vary, then PIVOT as per usual, I commented out the voucher date criteria because the sample dates weren't in proper format. If some projects are excluded entirely by the date requirement, then you will also add the date criteria to the top portion that sets the project list.

Upvotes: 1

Gatej Alexandru
Gatej Alexandru

Reputation: 92

If the number of values for project column are known and they are also few you can try this:

select category_id
    ,sum(case when project='pnr' then amount else 0. end) as pnr
    ,sum(case when project='npk' then amount else 0. end) as npk
    ,sum(case when project='jpg' then amount else 0. end) as jpg
from [yourtable]
where [voucher date] between @bd and @ed--@bd and @ed are the input interval
group by category_id

or to play with pivot command

Thanks

Upvotes: 0

Related Questions