Sorry for the delay. I had some deliverables and a few meetings. It was a fun little code challenge that I put on myself to finish it under 25 minutes.
Teddy On 1/16/07, Teddy Payne <[EMAIL PROTECTED]> wrote: > > 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:266712 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
