
Complex Joins in SQL Server using TSQL
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
Quote:
> Hi,
> 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.