> -------- Original Message --------
> Subject: (ot) Transact-SQL Help
> From: Rick Root <[email protected]>
> Date: Thu, September 10, 2009 12:25 pm
> To: cf-talk <[email protected]>
>
>
> 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:
>
After reading your post several times I think I might understand just
what it is you are trying to accomplish here.
Questions: What makes you think you need to do this in SQL?
Is the current code performing poorly?
Are there really hundreds of columns and this is just a simplified
example?
Are there really only 13 records in the ptTeam table or are you actually
doing dozens of times on one page?
Is there any particular difference between EXEC1 and EXEC2 or is it just
up to the randomness of the query as to who gets to be 1 and who gets to
be 2?
Do you know if you will have at least one of every type of person?
Since you asked-- as for SQL solutions, it looks like you are basically
wanting a column for each record matching a particular criteria. My
first thought is a messy select statement that joins to the ptTeam table
once for every column. This will actually be made harder if the order
of the members of the same type is not explicit.
Note: you would need to change each join that isn't guaranteed to return
a match to an LEFT OUTER JOIN. I'm also making the wild assumption that
your records all share the same entityid but have a unique MEMBERID.
You also didn't say what column you wanted to return for each member so
I assumed you wanted the MEMBERID column.
SELECT TOP 1 EXEC1.MEMBERID AS EXEC1,
EXEC1.MEMBERID AS EXEC2,
TEAMLEAD.MEMBERID AS TEAMLEAD,
etc...
FROM ptTeam EXEC1
INNER JOIN ptTeam EXEC2 ON EXEC1.entityid = 'foo'
AND EXEC1.status = 1
AND reltype='ETL'
AND EXEC1.MEMBERID <> EXEC2.MEMBERID
INNER JOIN ptTeam TEAMLEAD ON EXEC1.entityid = 'foo'
AND TEAMLEAD.status = 1
AND TEAMLEAD.leaderFlag=1
INNER JOIN etc...
WHERE EXEC1.status = 1
AND reltype='ETL'
AND EXEC1.entityid = 'foo'
For the record, I would expect this to perform like crap since it's
going to make every possible combination and then pick one to return
with TOP 1.
Moving on, another option would be to declare a table variable to hold
your final result set:
DECLARE @finalResult TABLE
(entityid int,
EXEC1 int,
EXEC2 int,
TEAMLEAD int,
MEMBER1 int,
MEMBER2 int,
MEMBER3 int,
MEMBER4 int
etc...)
Then populate your columns one statement at a time:
INSERT INTO @finalResult (entityid, EXEC1)
(SELECT entityid, MEMEBRID
FRKM PTRACK.dbo.ptTeam
WHERE status=1
AND reltype='ETL'
UPDATE fr
SET fr.EXEC2 = team.MEMBERID
FROM @finalResult fr
INNER JOIN ptTeam team ON fr.entityid = team.entityid
AND team.status=1
AND team.reltype='ETL'
AND fr.EXEC1 <> team.MEMBERID
UPDATE fr
SET fr.TEAMLEAD = lead.MEMBERID
FROM @finalResult fr
INNER JOIN ptTeam lead ON fr.entityid = lead.entityid
AND lead.status=1
AND lead.leaderFlag=1
etc...
Ok, now that I gave those examples let me say I think they're both
pretty crappy. Honestly I'd do this in CF if it were me based on what
you showed us, but I would do it with query of queries.
Return your 15 or so records in a single, simple hit to the database.
Then perform 6 qofqs on it-- one for each type of member. In other
words, get all the EXECs in one, and all team lead in another, and then
just loop over them as you go and output them as EXEC1, EXEC2, TEAMLEAD,
etc.
As long as you are only dealing with a dozen or so records from the
database it should perform fine and and I think it will be a heck of a
lot simpler than trying to make your SQL server take a row-based list of
people and pivot them out into columns. That never works easily because
it really isn't the kind of thing SQL Server was designed to do. On the
cursor thing-- that is another option I didn't present, but I really
don't think it's a better one.
Thanks.
~Brad
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:326214
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
