Tricky SELECT statement

[LV]

Hi all,

Here is the deal: I have two tables, program and agent. The primary key program_id in program table is the foreigh key in agent table. I need to retrieve a list of agents by program, but it has to be a "flat" one. I.e., instead of:

Program Agent
1 Bill
1 Jo
1 Matt

I need

Program Agent1 Agent2 Agent3
1 Bill Jo Matt

Is there any way to do that? Thanks!

 

[ESquared]

First you need to assign a sequence number to each record starting at 1 for each Program. See the derived table in the code below.

Then you need to do some case and group by statements like a crosstab query.

See the following to read up on crosstab / pivot queries.

Crosstab Query - PART I (Code Generator) faq183-5269
Crosstab Query - PART II (Dynamic Execution) faq183-5278
Dynamic Cross-Tabs/Pivot Tables

SELECT
    Id,
    Agent1 = Max(CASE AgentNum WHEN 1 THEN Agent END),
    Agent2 = Max(CASE AgentNum WHEN 2 THEN Agent END),
    Agent3 = Max(CASE AgentNum WHEN 3 THEN Agent END),
    Agent4 = Max(CASE AgentNum WHEN 4 THEN Agent END),
  FROM Agents A
    INNER JOIN (
      SELECT ID, AgentNum = Count(A2.ID)
      FROM Agents A1
      INNER JOIN Agents A2 ON A1.ID >= A2.ID
      GROUP BY ID
    ) C ON C.ID = A.ID
  GROUP BY Id

If you want to select a bunch of other fields along with the agents, then you should make this whole query a derived table and join it, so you don't have to group by every other field you choose, too.

-------------------------------------
A sacrifice is harder when no one knows you've made it.