Posted by Erland Sommarskog on 03/22/06 00:53

jeffvh (jeffvh.251ikz@no-mx.forums.yourdomain.com.au) writes:
> I have 2 tables related as:
>
> T1.KEY, T1.FIELD1, T1.FIELD2
>
> T2.KEY, T2.FIELDA, T2.FIELDB
> T2.KEY, T2.FIELDA, T2.FIELDB
>
> T1.KEY = T2.KEY
>
> I want to return a SELECT as:
>
> T1.FIELD1, T1.FIELD2, T2.FIELDA, T2.FIELDB, T2.FIELDA, T2.FIELDA
>
> The second table, in some cases but not all, has multiple rows for each
> row in T1. I want to return a single row with all values for T2.FEILDA
> and B.

So for T1.Key = 8 there are six rows in T2, there should be 14 columns,
two for T1 and seven for T2?

I'm afraid that is not easily doable.

The result of a query is alwys a table, and a table has a fixed number
of columns; it cannot be jagged.

It still possible to define a query that has maximum of columns needed,
but that number must be known in advance. You cannot write a query
which produces 16 columns on one execution, and 20 columns next time.

Furthermore, we need rules to say which row goes into which column.

So in the general case, this is very messy, and may be easier to sort
this out client-side.

However, if there are further conditions that you know, but didn't tell us,
it might be easier. The general recommendation is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of the busines problem.

--
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

[Back to original message]