Help required for a Kind of Cross tab Query

[asimasm]

Hi
I need to design a query:
Consider following data.
col1 col2
------------------------
1 1
1 2
2 11
2 12
3 50
4 101
4 102
4 103
Now i need to produce the result that would group the data on col1 but all the repeating result in col2 displayed in a single column. I.e the result of the above should be.
Newcol1 Newcol2(String Type)
------------------------
1 1,2
2 11,12
3 50
4 101,102,103

I have tried the transform query but it puts all the data as seperate columns.
If anybody has solution then plz do reply.
Thanks

 

[Kalor]

The only way I know to do it by a stored proc, ie use a cursor to loop through every record. If Col1 is the same as previous record's Col1 then append a Col2 string variable, else write the record to a temp table and start building a new Col2.

How many records are you dealing with? If there are quite a few you may reach the limit that a string can store...

 

[tlbroadbent]

Here is an example of a procedure that does what you want. It is similar to that descibed by Kalor.

-- Inhibit Rows Affected messages
Set nocount on
go

-- Declare variables required for process
Declare @Col1 Varchar(30)
Declare @all Varchar(8000), @Dlm Char(1)

-- Set value of delimiter to comma
Set @Dlm = ','

-- Create temp table
Create Table #Tbl
(Col1 char(4), NewCol2 Varchar(8000))

-- Create cursor for distinct values of Col1
Declare Col1_Cursor Cursor For
Select Distinct Col1 From tblName

Open Col1_Cursor

Fetch Next From Col1_Cursor Into @Col1
While @@Fetch_Status=0
Begin
Set @all=Null -- Initialize variable

-- Select and concatenate values of
-- Col2 for current value of Col1
Select @all = Coalesce (@all + @Dlm, '') +
ltrim(str(Col2))
From tblName
Where Col1=@Col1
And isnull(Col2,'')<>''
Order By Col2

If Left(@all,1)=@dlm Set @all=Stuff(@all,1,1,'')

-- Insert values into temp table
Insert #tbl Values (@Col1, @all)

-- Get next value of Col1 in Cursor
Fetch Next From Col1_Cursor Into @Col1
End

-- Close and deallocate cursor
Close Col1_Cursor
Deallocate Col1_Cursor

-- Create record set from temp table
Select Col1, NewCol2
From #tbl
Go

Set nocount off
go

-- Drop temp table
Drop table #tbl
go Terry L. Broadbent
Programming and Computing Resources

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

 

[asimasm]

Hi
Thanks for u'r replies. But i require only a single SQL query Bcz i also have to implement the solution in MS Access. If any one has solution as a single SQL query then that will work.

Thanks in Advance