x

Displaying fields from a result set on the same line

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

more ▼

asked Oct 29, 2009 at 01:37 PM in Default

Orbison gravatar image

Orbison
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Oct 29, 2009 at 02:08 PM

Aaron Alton gravatar image

Aaron Alton
565 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x984
x369

asked: Oct 29, 2009 at 01:37 PM

Seen: 2066 times

Last Updated: Oct 29, 2009 at 01:39 PM