• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1694
  • Last Modified:

Crosstab in Gridview?

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
gianitoo
Asked:
gianitoo
1 Solution
 
madhevan_pillaiCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now