Cross Tab query in SqlServer

[Darsin]

Hi,
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.

Thanks in advance

[Erland Sommarskog]

Darsin (da****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx