Click here to Skip to main content
14,388,768 members
Rate this:
Please Sign up or sign in to vote.
See more:
I wrote a query that gets the counts of which has different statuses.
The query goes like

SELECT COUNT(rec.rmaNum) as Created
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
WHERE rec.rmaStatus = 0 
UNION 
SELECT COUNT(rec.rmaNum) as Received
FROM RMARecords rec
LEFT JOIN RMAUsers ure on ure.id = rec.userCreated
WHERE (rec.rmaStatus = 1 OR rec.rmaStatus = 2)
UNION
SELECT COUNT(rec.rmaNum) as Closed
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 3
UNION
SELECT COUNT(rec.rmaNum) as Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 4


The output of this query is;

Created
-------
0
1
3
6

I think because of the union the result set comes like this. What I want as output is different

Created Received Closed Voided
--------/----------/-------/--------
---0---/----1-----/--3---/---6----

How can I get as a result set like the upper result? Thanks in advance.
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 1

Check this,

http://weblogs.asp.net/salimfayad/archive/2008/01/30/rows-to-columns.aspx[^]

It should help



*Mark as answer if this solves
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

try this one wonder-FOOL.

SELECT (SUM(CASE WHEN rec.rmaNum = 0 THEN 1 ELSE 0 END)) as 'Created',
(SUM(CASE WHEN rec.rmaNum IN ('1','2') THEN 1 ELSE 0 END)) as 'Received',
(SUM(CASE WHEN rec.rmaNum = 3 THEN 1 ELSE 0 END)) as 'Closed',
(SUM(CASE WHEN rec.rmaNum = 4 THEN 1 ELSE 0 END)) as 'Voided',
FROM RMARecords AS rec
LEFT OUTER JOIN RMAUsers AS usr ON rec.userCreated = usr.id
GROUP BY rec.rmaNum



If you have questions just comment, and if this is helpful mark as answer.

Best Regards,
@iamsupergrasya
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100