Richard,
I did not like the TSQL dynamic SQl that I created. I always hate defeating
an execution plan. Here is a CF way:
<!--- I used ad hoc queries for the sake of time. I highly recommend
putting this in a multi-result stored procedure --->
<cfquery datasource="test" name="qryEntry">
select
entryID
, entryCol1
from
dbo.tblEntry
</cfquery>
<cfquery datasource="test" name="qryEntryService">
select
e.entryID
, s.serviceCol1
from
dbo.tblEntry e
join dbo.tblEntryService es
on e.entryID = es.entryID
join tblService s
on es.serviceID = s.serviceID
</cfquery>
<cfquery datasource="test" name="qryService">
select
serviceID
, serviceCol1
from
dbo.tblService
</cfquery>
<cfset arData = ArrayNew(1)>
<!--- Create the empty service columns in your array --->
<cfloop query="qryEntry">
<cfset arData[qryEntry.currentrow] = StructNew()>
<cfset arData[qryEntry.currentrow].entryID = qryEntry.entryID>
<cfset arData[qryEntry.currentrow].entryCol1 = qryEntry.entryCol1>
<cfloop query="qryService">
<cfset arData[qryEntry.currentrow]["#qryService.serviceCol1#"] = " ">
</cfloop>
</cfloop>
<cfset found = 0>
<cfloop query="qryEntryService">
<!--- Find where entry is located in the array --->
<cfloop from="1" to="#ArrayLen(arData)#" index="i">
<cfif arData[i].entryID eq qryEntryService.entryID>
<cfset found = i>
</cfif>
</cfloop>
<!--- Update the X in the column that represents the column --->
<cfset arData[found]["#qryEntryService.serviceCol1#"] = "X">
<cfset found = 0>
</cfloop>
<cfdump var="#arData#">
My schema was:
CREATE TABLE [tblEntry] (
[entryID] [int] IDENTITY (1, 1) NOT NULL ,
[entryCol1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblEntry] PRIMARY KEY CLUSTERED
(
[entryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tblEntryService] (
[entryServiceID] [int] IDENTITY (1, 1) NOT NULL ,
[entryID] [int] NULL ,
[serviceID] [int] NULL ,
CONSTRAINT [PK_tblEntryService] PRIMARY KEY CLUSTERED
(
[entryServiceID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tblService] (
[serviceID] [int] IDENTITY (1, 1) NOT NULL ,
[serviceCol1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblService] PRIMARY KEY CLUSTERED
(
[serviceID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
On 1/16/07, Richard Cooper <[EMAIL PROTECTED]> wrote:
>
> That'd be great. I'm using SQL Server 2000.
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266709
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
