Grouping?

[Guest_imported]

I'm a newbie to sql obviously and here is my dilema:

Table 1
ID(KEY) Name
1 Country Place
2 Abbey Road
3 State House
4 Remington House
5 Stoneleigh2

Table2
Table2ID ID(Key) Type Price
1 1 1 500
2 1 1 550
3 1 1 600
4 1 2 650
5 2 1 800
6 2 1 500
7 2 1 600
8 2 3 695

How can I Select ID, Type Where Price is less than $700 for example. Where it would be in this format:

ID Type
1 1,2
2 1,3


If anyone understands what I'm trying to do and can help, it would save me a lot of grief.

Thanks a lot

 

[rac2]

It is practically impossible to construct a SQL query to do what you wish. SQL likes to display rows of data; it does not like to concatenate them as you wish.
This will get the data you want out of Table 2.

SELECT ID, Type
FROM Table2
WHERE Price < 700
GROUP BY ID, Type

With the SQL work done and the results stored in your application, write a procedure in your application to format the results as you wish.

 

[bperry]

Using cursors is not generally recommended, but in this case it would be quite easy to write a cursor (probably inside a stored procedure) to build the result set you wanted, including the concatenation, and return it to the process calling the SP.
(This would work in MS SQL Server; probably similar in Oracle?)

 

[tlbroadbent]

If the number of Types to be concatenated is not great you can get the result you need without cursors. Cursors wil be advatageous if the number of Types grows large. Here is an example query that will concatenate to 4 types.

Note this may not work in your RDMS.

--Create temporary work table
create table #tmp (ID int, Type int, RecNo int)

set nocount on

--Insert rows that meet the <700 criteria
Insert #tmp
Select ID, Type, 0
From table2
Where Price<700
Group By Id, Type

--Number the rows by ID
Update #tmp
Set RecNo=(Select count(*) From #tmp Where Id=t.ID And Type<=t.Type)
From #tmp t

--Select from four queries LEFT JOINED
--on ID. Each query selects a different
--Record Number (RecNo) from #tmp.
Select
a.ID,
Types=ltrim(str(a.Type)) +
case When b.type is null then '' Else ',' + ltrim(str(b.type)) End +
case When c.type is null then '' Else ',' + ltrim(str(c.type)) End +
case When d.type is null then '' Else ',' + ltrim(str(d.type)) End
From
(Select * From #tmp where recno=1) a
Left Join
(Select * From #tmp where recno=2) b
on a.ID=b.ID
left join
(Select * From #tmp where recno=3) c
on a.ID=c.ID
left join
(Select * From #tmp where recno=4) d
on a.ID=d.ID

drop table #tmp
set nocount off Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[bperry]

This is one of those situations where it would be interesting to do it both ways, to compare the performance of the cursor vs left-join approach. Although the LJ gets us away from a cursor, it requires x passes through the table (4 in the previous example) to build the result set. Also. A.Davis hasn't indicated the size of the tables involved, which could be an important factor
However, the LJ approach is quite interesting, if the number of types can be fixed.

 

[Aaron36]

Is it possible to group it by adding more columns and if so how would you do that? Illustration below



Table 1
ID(KEY) Name
1 Country Place
2 Abbey Road
3 State House
4 Remington House
5 Stoneleigh2

Table2
Table2ID ID(Key) Type_1 Type_2 Price
1 1 1 500
2 1 1 550
3 1 1 600
4 1 2 650
5 2 1 800
6 2 1 500
7 2 1 600
8 2 2 695

How can I Select ID, Type Where Price is less than $700 for example. Where it would be in this format:

ID Grouped Types 1 and 2
1 1,2
2 1,2

Thanks for looking at this,

A.Davis

 

[tlbroadbent]

Here is one possibilty. However, only one value of Type_1 and Type_2 per ID will be selected. Otherwise, you'll have to use a modifed version of the first query I supplied.

Select
ID,
Cast(Max(Type_1) as varchar(3)) +
Case When Max(Type_2) Is Null
Then ''
Else ',' + Cast(Max(Type_2) as varchar(3))
End
AS [Types 1 and 2]
From table2 Where price<700
Group By ID Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.