CODEWITHSUNDEEP
sqlvb.netwinformsdatagridviewpivot
SarathSprakash
SarathSprakash

Reputation: 4624

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.....

Upvotes: 0

Views: 1484

Answers (2)

Taryn
Taryn

Reputation: 247720

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 |

Upvotes: 1

sangram parmar
sangram parmar

Reputation: 8726

use pivot query to fetch data from sql..and get result from sql as you want

Upvotes: 0

Related Questions