I have a problem I want to solve in TSQL.  It is the standard join problem
experienced in relational database systems when a one to many join is added
to a query, but when you come to display the resulting recordset (in a
browser or report, etc.) you don't want that one to many join to create
duplicate records.  If this sounds a bit abstract, here is an example.  Say
for instance that you have a table called "Managers" and another table
called "Common buzzwords".  Any given manager has a finite set of common
buzzwords that they like to use.  Lets assume we performed a join on
"Mangers" and "Common buzzwords". The resultset may look like this:

Jim        Super Fun
Jim        Proactive, Not Reactive
Jim        Win Win Situation
Jim        Center of Excellence

Note, however, that in our report, we would like the data formatted like
thus:

Name        Common Buzzwords

Jim            "Super Fun", "Proactive, Not Reactive", "Win Win Situation",
"Center of Excellence"

In MS Access, you can construct a query which includes a calculated field.
This calculated field could call a user defined function which returned the
string above, given a particular manager id for each row in your query.

The question is whether you can do the equivalent of this using pure TSQL.
By building a stored proc which declares a cursor, you could iterate through
a recordset to build the string as stated above, but can you include calls
to this stored proc from within a SQL statement select list? The
documentation tends to imply that you can't.  Can anyone think how you might
solve this problem by simply using TSQL?  I know I can do this using ADO,
but I would prefer a pure TSQL solution.

TIA

Aaron.

    If you have a finite maximum of Common Buzzwords, then, yes, you can do
it. You would need to add another column to the Buzzwords table, call it
BuzzIndex, or whatever.

    For a maximum of 5 'Buzzwords' per manager (BuzzIndex could be 1 to 5)

     SELECT
        Managers.Name,
        Buzzword1 =
                COALESCE((SELECT Buzzword FROM Buzzwords WHERE
Buzzwords.ManagerID = Managers.ManagerID AND BuzzIndex = 1), ''),
 Buzzword2 =
                COALESCE((SELECT Buzzword FROM Buzzwords WHERE
Buzzwords.ManagerID = Managers.ManagerID AND BuzzIndex = 2), ''),
 Buzzword3 =
                COALESCE((SELECT Buzzword FROM Buzzwords WHERE
Buzzwords.ManagerID = Managers.ManagerID AND BuzzIndex = 3), ''),
 Buzzword4 =
  COALESCE((SELECT Buzzword FROM Buzzwords WHERE Buzzwords.ManagerID =
Managers.ManagerID AND BuzzIndex = 4), ''),
 Buzzword5 =
  COALESCE((SELECT Buzzword FROM Buzzwords WHERE Buzzwords.ManagerID =
Managers.ManagerID AND BuzzIndex = 5), '')

    FROM
        Managers

You could also perform a concatenation of the buzzwords, providing spacing
and commas, but this is the fastest solution, and you should have your
report do the comma separation and other logic. The problem that causes the
extra logic is this:

1 Buzzword only: Super,,,,,
 2 Buzzwords only: Super, Fun, , ,
etc...

You don't want that much logic in the T-SQL statement.

Regards,
D. Patrick Hoerter