I would like to create a query that would generate the output table, but I'm not sure where to start.

[USER TABLE]

userID fName lName
1 Joe Smith
2 Mike Brown

[CATEGORY TABLE]

categoryID title
1 category1
2 category2
3 category3

[USERCATEGORy TABLE]

userID categoryID
1 1
1 3
2 2
2 3

[OUTPUT TABLE]
(comma seperate rows

fName lName category1 category2 category3
Joe,Smith,x, ,x
Mike,Brown, ,x,x

This is a classic cross-tab query. There is a generic solution, and there are database engine specific solutions. The generic solution is a lot more work, and it is vulnerable to some kinds of data errors.

Can you give us a bit more background so we can help you out?

-PatP

The rdbms is ms sql 2000. I was debating wether to post it here or in that category. It will lead to an output as an excel file, that is why I want the x's in the category, but for now i'm just interesting the sql to generate this. If a sql solution is not advisable I welcome pointers in t-sql that might help me with this.

Try doing a search for cross-tab in the SQL Server forum. I've answered this type of question at least a dozen times there, you should be able to find a decent hit or three!

-PatP

Books On Line has an excellent explanation and example of Crosstab queries. Look it up.