By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,414 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,414 IT Pros & Developers. It's quick & easy.

Many to one Select

P: n/a

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.
--
jeffvh
------------------------------------------------------------------------
jeffvh's Profile: http://www.dbtalk.net/m47
View this thread: http://www.dbtalk.net/t293766

Mar 21 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
jeffvh (je***********@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, 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
Mar 21 '06 #2

P: n/a
this is called a "cross tab" report. very hard to do in sql.
do some research, and you can find some examples, but they all require
custom sql.

Mar 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.