Query

[bangalibhai]

Hi,

Is it possible to do the following by writing a SQL query.....

Beginning Table

Col1 Col2 Col3
=====================
2001 2378 212
2001 2378 213
2001 3019 218
2001 3019 236
2001 3019 220
2001 4112 239
2001 4112 231
2002 921 214
2002 1315 227
2002 1315 26
2002 2774 226
2002 2774 223
2002 2774 225
2003 4128 214
2003 4450 237
2003 4450 212
2003 4450 213
2003 4450 235


Resulting Table:

Col1 Col2 Col3
=====================
2001 2378 212,213
2001 3019 218,236,220
2001 4112 239,231
2002 921 214
2002 1315 227,26
2002 2774 226,223,225
2003 4128 214
2003 4450 237,212,213,235

Basically I am trying to GROUP BY Col1 AND Col2 and place the data of respective Col3 all together. I am developing this in MS SQL Server.

Your help is greatly appreciated.

Thanks,
Nihad

 

[sunila7]

Hi,

Have a look at this FAQ

faq183-2146

Is this wht u r looking for....

Sunil

 

[tlbroadbent]

faq183-1067 provide one method of doing what you need. The following script will also concatenate the col3 row data into a single column.

set nocount on

Declare @tmp1 table(Col1 int, Col2 int, Col3 int, RecNo Int)
Declare @tmp2 table(Col1 int, Col2 int, Col3List varchar(4000))

Insert @tmp1
Select Col1, Col2, Col3,
(Select count(*) From YourTable
Where Col1=a.Col1 And Col2=a.Col2 And Col3<=a.Col3)
From YourTable a

Insert @tmp2
Select Distinct Col1, Col2, ''
From YourTable

Declare @rn int, @mn int
Select @rn=1, @mn=Max(RecNo) From @tmp1

While @rn<=@mn
Begin
Update @tmp2
Set Col3List=Col3List+Case When Col3List='' Then '' Else ', ' End+Col3
From @tmp2 a
Join @tmp1 b
On a.Col1=b.Col1 And a.Col2=b.Col2
Where b.RecNo=@rn
Set @rn=@rn+1
End

Select * from @tmp2
order by Col1, Col2

NOTE: If you don't use SQL Server 2000, change the table variables to temp tables. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[bangalibhai]

Thank you for all your help. I do have my own script to solve the problem, which is similar to what &quot;tlbroadbent&quot; provided. From the simplicity of the problem, I was under the impression that it could be solved by writing a query with a sub-query in it.

Nihad