August 21, 2003 at 1:17 am
Hai,
I have a table with following Data.
col1 col2
---- ----
1 a
2 b
1 d
3 a
2 c
I want the result of the query as
Col1 col2
--- ----
1 a,d
2 b,c
3 a
I am able to get this result with the help of cursor. Is there any other method to get it.
Regards,
Ramesh
August 21, 2003 at 5:25 am
Ramesh,
One set-based way to do it without a cursor is to use a UDF to return the concatenated col2 fields for each col1.
Cheers,
- Mark
Cheers,
- Mark
August 21, 2003 at 6:26 am
Thanx mark. I tried Your solution and it worked out.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 21, 2003 at 8:13 am
Here is another way:
-- create tables
CREATE TABLE test(col1 int, col2 char(1))
-- populate the table
insert into test values(1,'a')
insert into test values(2,'b')
insert into test values(1,'d')
insert into test values(3,'a')
insert into test values(2,'c')
-- declare variables
declare @p char(1000)
declare @top int
declare @c1 int
declare @sm-2 int
declare @c2 char(1)
-- Print Report Heading
print 'Col1 ' + 'Col2'
print '---- ' + '----------------'
set @p = ''
select top 1 @top = col1 from test order by col1 desc
-- set @c1 to the first id number
select top 1 @c1 = col1, @c2=col2 from test order by col1
-- Process until no more
while @c1 <= @top
begin
-- string together all items with a comma between
select @p = rtrim(@p) + ', '+ col2
from test a
where col1 = @c1
-- print detail row
print cast(@c1 as char(1)) + ' ' + rtrim(substring(@p,3,len(@p)))
-- increment clientid number
set @sm-2 = @c1
select top 1 @c1 = col1, @c2=col2 from test where col1 > @c1 order by col1
set @p = ''
if @c1 = @sm-2 set @c1 = @top + 1
end
-- REMOVE EXAMPLE TABLE
DROP TABLE test
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 22, 2003 at 3:45 am
Thanx mark and Larsen. I tried Your solution and it working fine.
August 22, 2003 at 10:49 am
SELECT Table4.col1, IIf([FirstOfCol2]=[LastOfCol2],[FirstOfCol2],[FirstOfCol2] & [LastOfCol2]) AS Col2, First(Table4.col2) AS FirstOfcol2, Last(Table4.col2) AS LastOfcol2
FROM Table4
GROUP BY Table4.col1;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply