Solved

Crosstab  in Gridview?

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Set cookies HttpOnly and Secure 4 108
Response.Redirect Open new Form/tab/page? 5 30
Re-position the objects 7 96
Jquey and footrable 2 34
AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
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…
A short film showing how OnPage and Connectwise integration works.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now