Displaying fields from a result set on the same line

Question

Hi Guys, I have the following SQL Statement which produces 4 lines of output for two stores A and B. Record 1 output belongs to Store A and records 2,3 and 4 belong to Store B.

Select c.Commodity_Name, pr.Product_Description, pc.Product_ID,
pc.Price 
from commodity c,
Pricing pc,
Description pr,
Store s
Where c.commodity_ID = pc.Commodity_ID
and pc.Store_ID = s.Store_ID
and s.Store_Name in ('Store A', 'Store B');

Is it possible to display the above fields for only record 1 and only the price field for records 2, 3 and 4 on the same line using SQL?

Thanks

Answer 1

+1 on aliasing each table

+1 on the semicolon terminator

-1000 on the SQL-89 syntax. Time to switch over to SQL-92 (explicit joins). Like this:

SELECT  c.Commodity_Name, pr.Product_Description, pc.Product_ID, pc.Price
FROM    Store s
JOIN    Pricing pc ON s.Store_ID = pc.Store_ID
JOIN    Commodity c ON pc.Commodity_ID = c.Commodity_ID
JOIN    [Description] pr ON ?????????
WHERE   s.Store_Name IN ('Store A', 'Store B')

You'll notice that the explicit joins make it painfully obvious that you've forgotten to "join" description to anything else.

Now back to your question, yes it is possible. Are you always going to have one line from Store A, and two from Store B? If so you can "join back" to Pricing three times and hard code some constraints. I can't imagine that you're really looking to do that though - more likely, you want to look at a dynamic pivot.

Displaying fields from a result set on the same line

Follow this question

Related Questions