concatenate output records from SQL Query?

Orbie

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 31 2.99
2 AIRFRESHNER 200ML MORNING MIST 42 1.99
3 AIRFRESHNER 200ML MORNING MIST 83 3.99
4 AIRFRESHNER 200ML MORNING MIST 94 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 one SQL statement?

Thanks.

Tom Dunne

What DBMS are you using (not that it really matters)?

Google Rows to Columns, I had to do that some time back and I found some code out there that would do what you require.

Edit: First hit on Google for an Oracle database.

DublinWriter

Orbie wrote: »

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 one SQL statement?

I think by 'one SQL statement' you mean return the results in one row?

The answer is no, even using a sub-query on the pricing table, a SQL statement has to return a discrete set of fields and you could have any number of prices being returned, or in other words, an SQL statement can't return a variable set of columns based on conditionality.

Having said that, you might be able to do some jiggery-pokery using a UNION clause, but it depends on your DBMS.

stephenlane80

That can be a common enough problem for data reporting etc,

What you need to do if first do a group by query grouping on the first 5 columns, to find all of the records with the same first 5 columns

In you example they will all be the same, so you get one result,

What you need to do with that resultset is loop through all the results (one in you case ) with a cursor. Then with each record (one in your case) create a new cursor where you wil loop through the results from the original table that have the 5 columns you are looking at. In your case you will be looping through all the records in the table cause they all ahve the same first 5 columns, and as you loop, you can concatonate the values in column 6 into varchar/string that has global scope so you dont overwrite the previous value with each increment.

So in summary you will need to generate a distinct data set that share the same first 5 columns, and use 2 nested cursors (like a for each loop) to concatonate the rows in row 6.

After each increment of the first sursor you can output the first 5 rows (store them in variables) as well as you concatonated values from row 6,

Do not use this method on very large tables cause performace will suck, you are probable better off doing this in the application layer rehter then in the database, i have an example in T-SQL on my work pc if your interested,

mark renton

cant really fully understand question - but below code will give 1 commodity name - if i have this right you will also need to remove description and product id

Select decode(row_number() over (partition by c.Commodity_Name order by c.commodity_name),1,c.commodity_name, null) as 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');

DaveyByrne

If this is in SQL Server you can use a Pivot Table... I suggest you google it, since there isn't enough time/room here to explain how it works.

AndrewMc

Orbie wrote: »

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 31 2.99
2 AIRFRESHNER 200ML MORNING MIST 42 1.99
3 AIRFRESHNER 200ML MORNING MIST 83 3.99
4 AIRFRESHNER 200ML MORNING MIST 94 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 one SQL statement?

Thanks.

In MySQL something like the following should work. If you mean that all four lines should collapse to one, and that you identify identical products with the c.Commodity_Name and pr.Product_Description fields:

Select c.Commodity_Name, pr.Product_Description, pc.Product_ID, GROUP_CONCAT(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')
GROUP BY (c.Commodity_Name, pr.Product_Description);

If you want it broken up on a per-store basis, add s.Store_ID to the GROUP_BY list (and also to the selected fields).


I'm not sure I understand how the pr table is meant to be joined in? There's no conditions on it which I think means you'll have a inner (cartesian product) join which I greatly doubt is what you want?

Orbie

Hi Guys,
Thanks for getting back to me and apologies for the delay. I'm using SQL Server 2008. I've had a change in requirements so what i'm now looking to do is to pivot the following Table so the 4 lines of output for each product appear on the one line:

Price_ID (PK) Product_ID Estimated_Price Actual_Price Size Prod_Desc_ID (FK) Store_ID (FK)
10243 111111111 3.15 0 2562 1
10244 0 2.99 101G 2562 2
10245 0 3.99 102G 2562 3
10246 0 4.99 103G 2562 4
34639 222222222 1.84 0 2712 1
34640 0 5.99 201G 2712 2
34641 0 6.99 202G 2712 3
34642 0 7.99 203G 2712 4


Output should look like the following:

Product_ID Description* Estimated Price Actual Price Size Actual Price Size Actual Price Size
111111111 CAKES 3.15 2.99 101G 3.99 102G 4.99 103G
222222222 CRISPS 1.84 5.99 201G 6.99 202G 7.99 203G

*The Descrition fields (CAKES and CRISPS) are held on another Table and joined through the Prod_DESC_ID field.

Any ideas?

mark renton

alias the table as a, b, c, etc and when you go to select the second, third fourth Actual Price and Size then base on store number (only if all store numbers are in sequence) eg b.actual_price where store_number = a.store_number +1

Orbie

Hi John,
Can you explain with an example please?

Thanks

mark renton

sorry dude - at another look, seems that may not work

loking at your table im hoping this is not a real world scenario :eek:

Orbie

It certainly is John, why?

mark renton

Just looking here it seems to me that your relationship between your product table and your price table is incorrect


your product id should not only be entered for 1 prod_desc and null for the remainder

also your PK of this price table is only a PK of the attributes estimated_price and actual_price, so you can then have an actual price of 2.99 with 2 or many more PK's - if you do a query of all items with price 2.99 for example you will logically receive many duplicates