Solved

Crosstab  in Gridview?

Posted on 2008-10-28
1
1,651 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

717 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