multi-field query - SQL Server Forums

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

Transact-SQL (2000)

multi-field query

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2003-10-15 : 20:48:59

Hi!!
I have the next query:
SELECT COMPANY, ITEM,
OEBASE.CUR_PRICE_01
FROM OEBASE
WHERE OEBASE.COMPANY IN (2000,2001,2002) AND OEBASE.BASE_NAME IN ('BASE-OCJ', 'BASE-OSI','BASE-OCR')

This returns for each item three lines
2000 0010041 12.34
2001 0010041 12.74
2002 0010041 11.99

I want one row per item, how can I do that?

0010041 12.34 12.74 11.99

Put the price of each company on three different fields

Thanks!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-15 : 20:53:15

Have a look at this article:

[url]http://www.sqlteam.com/item.asp?ItemID=11021[/url]

It'll put the results in one row, but they will be comma separated. So just remove the comma from the query.

Tara

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-16 : 01:21:04

If you want them on three different fields, you'll need either correlated subqueries or a CASE statement:

SELECT ITEM,
MAX(CASE WHEN COMPANY=2000 THEN OEBASE.CUR_PRICE_01 ELSE 0 END) AS Price2000,
MAX(CASE WHEN COMPANY=2001 THEN OEBASE.CUR_PRICE_01 ELSE 0 END) AS Price2001,
MAX(CASE WHEN COMPANY=2002 THEN OEBASE.CUR_PRICE_01 ELSE 0 END) AS Price2002
FROM OEBASE
WHERE OEBASE.COMPANY IN (2000,2001,2002) AND OEBASE.BASE_NAME IN ('BASE-OCJ', 'BASE-OSI','BASE-OCR')
GROUP BY ITEM

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources