Hi Rick, This is where the limiting nature of SQL comes in--SQL is a set language and has no (or very limited) looping/reshaping capability. Unless MS has extended their SQL in ways I'm not aware of, this would be nearly impossible. That's where the power of CF comes in.
--Ben Rick Root wrote: > I'm hoping someone here can point me in the right direction. I'm > doing something in CF that I really need to be doing in SQL: > > I need to flatten this data: > > select > A.entityid, > A.MEMBERID, > A.RELTYPE, > A.leaderFlag > from > PTRACK.dbo.ptTeam A > WHERE > A.status=1 > > into this format: > > entityid, > EXEC1, (1st row reltype='ETL') > EXEC2, (2nd row reltype='ETL') > TEAMLEAD, (1st row leaderFlag=1) > MEMBER1, (1st row reltype='TMM' and leaderflag=0) > MEMBER2, (2nd row reltype='TMM' and leaderflag=0) > MEMBER3, (3rd row reltype='TMM' and leaderflag=0) > MEMBER4, (4th row reltype='TMM' and leaderflag=0) > MEMBER5, (5th row reltype='TMM' and leaderflag=0) > AFSTAFF1, (1st row reltype='AFS') > AFSTAFF2, (2nd row reltype='AFS') > AFVOL,, (1st row reltype='AFV') > DOCTOR1,, (1st row reltype='AMD') > DOCTOR2,, (1st row reltype='AMD') > > > The query returns multiple rows per entity. The output will be one > row per entity. > > In CF, I'm looping over the first query, and creating a resultset that > looks like the second query. If an entity has more than 1 executive > team leader or more than 5 team members or more than 2 doctors > associated, we ignore the extras. > > But I don't have the slightest friggin' clue how to do this in SQL. > > My db is MS SQL Server 2005 > > Thanks.... > > -- Ben Conner b...@webworldinc.com Web World, Inc. 888-206-6486 PO Box 1122 480-704-2000 Queen Creek, AZ 85242 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326208 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
