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....
--
Rick Root
CFFM - Open Source Coldfusion File Manager
http://www.opensourcecf.com/cffm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:326206
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
