Solved

Crosstab  in Gridview?

Posted on 2008-10-28
1
1,641 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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