One to Many Join consolidated to one row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

One to Many Join consolidated to one row

Author

Topic

AlexanderDelarge
Starting Member

3 Posts

Posted - 2011-06-14 : 10:39:04

I'm working in a progress 9.1D database. I can't query from access as one table has over 32 indexes and it will not allow me to link or export via ODBC connections. However SQL logic should work. There are about 5 tables being joined on various keys. When a simple AND is used to join the keys among the tables (in some form of progress or crystal report queries) i get up to 5 records where only the quantity and the price are different. For example this is what is returned when I query

Key Part Item Data QTY Price
1 Rod 12345 1/1/2000 1 100
2 Rod 12345 1/2/2000 2 110
3 Rod 12345 1/2/2000 3 120
4 Rod 12345 1/2/2000 4 130
5 Rod 12345 1/2/2000 5 140

This is what I want

Key Part Item Data QTY Price QTY1 Price1 QTY2 Price2 QTY3 Price3 QTY4 Price4
1 Rod 12345 1/1/2000 1 100 2 110 3 120 4 130 5 140

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-06-14 : 11:24:37

a) we're a MSSQL Site - progress database is different software so expert knowledge here limited.
b) expand on the following
select *
from table1 a
left join table1 b on b.part = a.part and b.item = a.item and b.data = a.data and b.key = a.key + 1

c) post what code you have.
d) your data samples aren't great - "key = qty" ,etc....that makes it hard to solve this.

kalinbac
Starting Member

6 Posts

Posted - 2011-06-14 : 11:34:41

hi, i have a similar problem.
table1: Profile ID, FirstName,...etc.
table2: ProfileCommunication ID, ProfileID, CommunicationInfo...etc
every profile has more than one communicationinfo like mobile, phone, email....
i want to select one profile's email, mobile, phone, etc.. information in a single row but in separate columns.
is there a way out?

If there is nothing to do, there is nothing to do...

AlexanderDelarge
Starting Member

3 Posts

Posted - 2011-06-14 : 11:48:12

A variation of this query

For each QuoteQty Where QuoteQty.QuoteNum = QuoteDtl.QuoteNum and QuoteQty.QuoteLine = QuoteDtl.QuoteLine no-lock,
each QuoteHed Where QuoteHed.QuoteNum = QuoteDtl.QuoteNum no-lock,
each Customer Where Customer.CustNum = QuoteDtl.CustNum no-lock,
each Part Where Part.PartNum = QuoteDtl.PartNum no-lock,
each PartPlant Where PartPlant.PartNum = Part.PartNum no-lock:

Produces something similar to this but with more fields

Quote Date Part QtyNum Qty Price Reference
17048 5/23/11 50R410 1 11.00 1200 6000318761
17048 5/23/11 50R410 2 21.00 1200 6000318761
17048 5/23/11 50R410 3 41.00 1200 6000318761
17048 5/23/11 50R410 4 61.00 1200 6000318761
17048 5/23/11 50R410 5 81.00 1200 6000318761

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources