Query conundrum

[phildude]

Hi, I have tables A and B. A has data like an ID, and some other fields, B has the same ID, and other fields. I'm trying to produce a view that combines the two horizontally to make it easier for reporting for some of my users. In essence, A has:
1
2
3

B has
1,10
1,20
1,30
2,5
2,6
2,7

I'd like to create a view that looks like
1,10,20,30
2,5,6,7

Any thoughts?
Phil

 

[tlbroadbent]

You want a crosstab query. This type of question is asked frequently in this forum. Use keyword search to find some threads that discuss crosstabs. Here are some examples.

thread183-451930: Crosstab queries
thread183-392875: Create Crosstab Using SQL Server View
thread183-445087: Best approach - crosstab?
thread183-388437: How do I return multiple counts of items?




If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[tlbroadbent]

Disregard my previous answer. You really don't want a crosstab in this situation. Check the following FAQs for some ways to accomplish what you want.

faq183-2146: Concatenating a one-to-many relationship into a single column

faq183-1067: How to organize SUM() for character field in SQL Server SELECT query

If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm