Solved

Crosstab  in Gridview?

Posted on 2008-10-28
1
1,625 Views
Last Modified: 2012-06-27
simple query  
select itemgroup,item from table


itemgroup  |   group
somegroup       a
somgroup         b
somegroup        c
anothergroup     A
anothergroup     B
anothergroup     C

I  want my grid to look like this but i dont know what the group names will be

Somegroup  |  anothergroup   |  thirdgroup
a                             A                        chino
b                             B                        cholo
c                             C



Can you provide code sample? or suggest a better approach with any of the 2.0 controls in asp.net
0
Comment
Question by:gianitoo
1 Comment
 
LVL 12

Accepted Solution

by:
madhevan_pillai earned 500 total points
ID: 22829267
Hi,

try this

table structure

ID      int      no      4
GroupName      varchar      no      50
Category      varchar      no      5

write a stored procedure

CREATE PROCEDURE [Cross] AS
BEGIN

DECLARE @t2 TABLE (ID int IDENTITY(1,1), Column2 nvarchar(255))
INSERT @t2 (Column2)(SELECT DISTINCT GroupName FROM CrossTab)
DECLARE @SQL nvarchar(max)
SET @SQL = 'SELECT  '
DECLARE @Max int
DECLARE @Column nvarchar(255)
DECLARE @i int
SET @i = 1
SELECT @Max =MAX(ID) FROM @t2
WHILE @i <= @Max
BEGIN
      SET @Column = (SELECT Column2 FROM @t2 WHERE ID = @i)
    SET @SQL = @SQL + 'CASE WHEN GroupName  = ''' + @Column + ''' THEN  Category ELSE '' '' END AS [' + @Column + ']' + CHAR(13)
      SET @i = @i + 1
    IF  @i <= @Max
            SET @SQL = @SQL + ','
END
SET @SQL = @SQL + ' FROM CrossTab '  --GROUP BY GroupName'

EXEC (@SQL)

END





bind the result to grid that is all

Madhevan
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question