|
|
Posted by Erland Sommarskog on 11/26/06 20:09
Darsin (darsin@gmail.com) writes:
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
>
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
>
> and so on....
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
SELECT U.userName,
Division1 = coalesce(MAX(CASE do.divisionID WHEN 1 THEN 1 END), 0),
Division2 = coalesce(MAX(CASE do.divisionID WHEN 2 THEN 1 END), 0),
Division3 = coalesce(MAX(CASE do.divisionID WHEN 3 THEN 1 END), 0)
FROM Users U
LEFT JOIN DivisionOfUsers do ON U.userId = do.userId
GROUP BY U.userName
The MAX in this query is somewhat of a trick. Each CASE expression returns
a non-NULL value for at most one row. So whether we use MIN - or even AVG -
does not matter. But by using MAX and GROUP BY, we don't need to left-join
for each division.
As you might understand from the query, it only handles a known set of
divisions. There is no way to write a query that handles an unknown
number of divisions. That would be a fundamental breach of the relational
foundations: a query returns a table, and a table has a finite number of
columns.
The only way to get an output if the possible columns are not known
beforehand is to use dynamic SQL to build a query like the one above.
Rather than endulging in dynamic SQL yourself, you may want to take a
look at the third-party tool RAC, http://www.rac4sql.net/.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|