Hello Martin,
Assuming (yes, i know) that you know all the possible values of the 'APP'
column, you could acheive this with correlated subqueries:
SELECT DISTINCT username,
(SELECT SUM (appcount)
FROM myTable indata
WHERE indata.username = myTable.username
AND indata.app = 'mail') AS mail,
(SELECT SUM (appcount)
FROM myTable indata
WHERE indata.username = myTable.username
AND indata.app = 'web') AS web,
(SELECT SUM (appcount)
FROM myTable indata
WHERE indata.username = myTable.username
AND indata.app = 'other') AS other
FROM myTable
If you don't know all the values of 'APP', but you are only interested is a
fixed set of values, you can lump all others together by changing the third
subquery to something like:
(SELECT SUM (appcount)
FROM myTable indata
WHERE indata.username = myTable.username
AND indata.app NOT IN ('mail','web')) AS other
This isn't a real pivot solution: you can't add an arbitrary number of columns
to the resultset based on the number of different values of the 'APP' column AT
RUNTIME...
/t
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:247027
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
