Hello Tanguy.
Thanks for the response. Yeah I found a similar example on the web. Yeah it
is a bummer that you need to know the field names but I have done a fudge by
running a query to get all field names and then using this to build the query
dynamically.
Thanks for the query that is exactly what I need.
My implementation, if you are interested, follows. And again thank you.
<!--- get all that should be used for field cols --->
<cfquery name="getConfNames" datasource="dsn">
SELECT DISTINCT(eicStats."Field4")<!--- honest, I did not name the
fields --->
FROM
eicStats
ORDER BY UPPER(eicStats."Field4")
</cfquery>
<!--- stuff to quote reserved words for col names --->
<cfset arReserve = arrayNew(1)>
<cfset arCount = 1>
<cfset arReserve[arCount] = "Create">
<cfset arCount = arCount + 1>
<cfset arReserve[arCount] = "Directory">
<cfset arCount = arCount + 1>
<cfset arReserve[arCount] = "Open">
<cfset fldList = ValueList(getConfNames.Field4,",")>
<cfloop from="1" to="#arrayLen(arReserve)#" index="i">
<cfif findNoCase(arReserve[i],fldList) GT 0>
<cfset fldList =
replaceNoCase(fldList,arReserve[i],'"#arReserve[i]#"','ONE')>
</cfif>
</cfloop>
<cfquery name="eicCrossDyn" datasource="#Application.firstDsn#">
select
user_name,
#fldList#
FROM(
select
user_name,
<cfset fldCount = 1>
<cfloop list="#fldList#" delimiters="," index="fld">
<cfoutput>
sum(case when "Field4"='#replaceNoCase(fld,'"','','ALL')#' then
formatCount else null end) #fld#
<cfif fldCount LT listLen(fldList)>,</cfif><!--- there must be a better
way to do this --->
<cfset fldCount = fldCount + 1>
</cfoutput>
</cfloop>
FROM
eicStats
GROUP BY user_name
ORDER BY UPPER(user_name)
)
</cfquery>
> 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...
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:247036
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
