Convert a Group By Row Result into Columns (holy grail...)
-
Hi guys (and gals),
This is my first post, and the reason I have joined your community.
I have a query table with the following info in it:-
- AgentID EventID
- ===================
- 2001 A
- 2002 A
- 2001 A
- 2001 B
- 2002 E
- 2002 F
- 2002 G
and what I would like to do is return:-
- AgentID A B E F G
- ========================================
- 2001 2 1 0 0 0
- 2002 1 0 1 1 1
At the moment I am using something like:-
Select AgentID, Sum(Case When EventID='A' Then 1 Else 0 End) as A, {repeat for every bloody letter of the alphabet} Group By AgentID, Order By AgentID
Now this works fine, but I can't believe that there is not a simpler way of doing this, i.e. something like: Select AgentID, CountOfGroupingOfColumn(EventID) - If you get the picture....
In any case my 26 Select clause works.... it just seems a bit rubbish thats all....
Comments greatly appreciated.
Paul.
-
Paul,
I've been writing T-SQL for about a 2yrs now and I've never seen such a function like that. If you find one let me know because I make full use of the CASE statement all the time, and things can get kinda hairy.
Brad
-
This is a classic Crosstab query.
Search on this forum for "Crosstab" and you will come up with some tips, tricks and procs.
eg.
http://www.sqlservercentral.com/scripts/contributions/936.asp
/rokmooose
You must unlearn what You have learnt -
Ok... I can see that working (although all it does is create the same query I would have created anyway....)
But how does that translate to a single ADO type Query from ASP; ie.
MyRst.Open "Select * from...."
Regards,
Paul.
-
Also....
I need it to return events that are not present;
AgentID A B C .... Z
with zero's in the empty columns....
I am now assuming I need a while / wend type thing? - I can program this server-side in ASP, but I was hoping for a more 'elegant' solution...
-
Yup crosstabs is not a forte in SQL :-(, it could be easier to manage in code...
What You could do is write a method (in VB/C#... ) that takes a recordset:
Rowtext, Coltext, Value
2001 A 2
2002 A 1
2001 B 1
2002 B 0
.
.
2001 E 1
2002 E 1
2001 F 0
2001 F 0
2002 F 1
2001 G 0
2002 G 1
.
.
And transforms this to a crosstab:
public static DataTable Crosstab( DataTable tableToCrosstab ){...}
This You could reuse for crosstab purposes,
The SQL would be responsible for just retrieving the data in the specified format: Rowtext, Coltext, Value.
( I know I did something similar once for crosstabing in an app,
SQL for dataretrieval, Method for crosstab formatting in app 
/rockmoose
You must unlearn what You have learnt -
If the purpose for producing the cross-tab is to generate a report then most reporting tools (SQL RS or Crystal) will produce the cross-tab for you. YOu can even pull the data into Access via a linked table in Access and it will easily cross-tab the data.
It all depends on what your need is...
-
You can use dynamic sql
---------------------------
DECLARE
@CHAR VARCHAR(50),
@TCHAR VARCHAR(50),
@CHARLEN INT,
@BEGIN INT,
@END INT,
@Eventlist varchar(100),
@eventcount int,
@sql VARCHAR(5000),
@MYSQL VARCHAR(200)
SET @sql=''
set @eventlist=''
SELECT @EventLIST=@EventLIST+ISNULL(S.eventid,'')+',' FROM
(SELECT DISTINCT eventid FROM agentevents ) S
SET @MYSQL='SUM(CASE WHEN eventid = ''XYZ'' THEN 1 ELSE 0 END)'
--print @mysql
SET @CHARLEN = LEN(@eventlist)
SET @BEGIN = 1
SET @END = CHARINDEX(',',@eventlist)
WHILE @END < @CHARLEN
BEGIN
IF @END = 0
BEGIN
SET @CHAR = RTRIM(@eventlist)
END
SET @END = CHARINDEX(',',@eventlist, @BEGIN)
IF @END = 0
BEGIN
SET @CHAR = SUBSTRING(@eventlist,@BEGIN,@CHARLEN - @BEGIN + 1)
SET @sql =@SQL+ ','+REPLACE(@MYSQL,'XYZ',@CHAR) + 'AS ' + @CHAR
BREAK
END
SET @CHAR = SUBSTRING(@eventlist,@BEGIN,@END - @BEGIN)
SET @BEGIN = @END + 1
SET @sql =@SQL+ ','+REPLACE(@MYSQL,'XYZ',@CHAR) + 'AS ' + @CHAR
END
SET @sql =RIGHT(@SQL,LEN(@SQL)-1)
SELECT @sql='Select Agentid ,'+@SQL+' FROM AgentEvents
GROUP BY Agentid'
exec(@sql)
----------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply