SarathSprakash SarathSprakash - 4 months ago 9
SQL Question

How to convert rows to columns in DataGridView

I have a datagridview with

CATEGORY PROJECT AMOUNT
======== ======= ======
Miscellaneous project1 1200
Miscellaneous project2 4500
Travel project3 3000
Miscellaneous project4 2300
Travel project4 1000


I want to convert the above format to

CATEGORY project1 project2 project3 project4
======== ======== ======== ======== ========
Miscellaneous 1200 4500 0 2300
Travel 0 0 3000 1000


The project_name is dynamic, it can change

pls help.....

Answer

Since you are using SQL Server you can use the PIVOT function to transform the rows of data into columns.

If you have a known number of values, then you can hard-code the query:

select category,
  coalesce(project1, 0) project1,
  coalesce(project2, 0) project2,
  coalesce(project3, 0) project3,
  coalesce(project4, 0) project4
from 
(
  select category, project, amount
  from yt
) d
pivot
(
  sum(amount)
  for project in (project1, project2, project3, project4)
) piv;

See SQL Fiddle with Demo.

But in your case it sounds like you will have an unknown number of values, as a result you will have to implement a dynamic SQL solution:

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

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

select @colsNull = STUFF((SELECT distinct ', Coalesce(' + QUOTENAME(PROJECT) +', 0) as '+ QUOTENAME(PROJECT)
                    from yt
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT category,' + @colsNull + ' 
            from 
            (
                select category, project, amount
                from yt
            ) d
            pivot 
            (
                sum(amount)
                for project in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo. Both will give a result:

|      CATEGORY | PROJECT1 | PROJECT2 | PROJECT3 | PROJECT4 |
-------------------------------------------------------------
| Miscellaneous |     1200 |     4500 |        0 |     2300 |
|        Travel |        0 |        0 |     3000 |     1000 |