SQL Query to merge output into a single line

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

General SQL Server Forums

New to SQL Server Programming

SQL Query to merge output into a single line

Author

Topic

Orbison
Starting Member

14 Posts

Posted - 2009-10-30 : 06:22:59

Hi Guys,
I have the following SQL Statement which produces the result set in the table below for two stores A and B. Record 1 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');

Rec # Commodity_Name Product_Description Product_ID Price
1 AIRFRESHNER 200ML MORNING MIST 28383838 2.99
2 AIRFRESHNER 200ML MORNING MIST 37378292 1.99
3 AIRFRESHNER 200ML MORNING MIST 47647643 3.99
4 AIRFRESHNER 200ML MORNING MIST 56534135 4.99

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

Commodity_Name Product_Description Product_ID Price (Rec 1) Price (Rec 2) Price (Rec 3) Price (Rec 4)
AIRFRESHNER 200ML MORNING MIST 28383838 2.99 1.99 3.99 2.99

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 08:40:00

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Orbison
Starting Member

14 Posts

Posted - 2009-10-30 : 09:20:51

Madhivanan, thanks for responding to my Query. I had a quick look at your post and just wondering what syntax your referring to? Will i need to use the 'union all' statement on each of the fields i want to merge i.e. Commodity Name and Description?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 09:24:10

No. Just use the SELECT query by replacing the column and table names

Madhivanan

Failing to plan is Planning to fail

Orbison
Starting Member

14 Posts

Posted - 2009-10-30 : 10:03:08

Sorry madhivanan, could you be a bit more specific?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-31 : 02:43:57

In the link, use only the select query. But replace the column name and table name

Madhivanan

Failing to plan is Planning to fail