Multiple Table Combination

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

Multiple subreports in a report

I'm trying to generate two reports from one reporting services URL, so
I created a "master report" that simploy contains two sub-reports.
When I run it in the preview browser, one subreport runs, and not the
2nd.
In the RS Manager, both subreports say nothing but "Error: Subreport
could not be shown".
I wonder if it's a datasource problem, or something else?
Thanks,
JackI imagine that it is a datasource issue, if it runs fine in report designer
preview.
I have a report that has 5 subreports and the only problem/change that I had
to make was to make sure that the data source is set properly for all the
subreports. For some reason it looses it occasionally when the reports are
deployed.
"jackfreud" wrote:
> I'm trying to generate two reports from one reporting services URL, so
> I created a "master report" that simploy contains two sub-reports.
> When I run it in the preview browser, one subreport runs, and not the
> 2nd.
> In the RS Manager, both subreports say nothing but "Error: Subreport
> could not be shown".
> I wonder if it's a datasource problem, or something else?
> Thanks,
> Jack
>|||Just had similar issue. Make sure the subreport operates standalone, with the
correct datasource, and proper parameters are passed to the sub report
property on the main report.
"jackfreud" wrote:
> I'm trying to generate two reports from one reporting services URL, so
> I created a "master report" that simploy contains two sub-reports.
> When I run it in the preview browser, one subreport runs, and not the
> 2nd.
> In the RS Manager, both subreports say nothing but "Error: Subreport
> could not be shown".
> I wonder if it's a datasource problem, or something else?
> Thanks,
> Jack
>