Post reply

help

  • August 21, 2003 at 1:17 am

    #160557

    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

    #470736

    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

    #470746

    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

    #470777

    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

    #470944

    Thanx mark and Larsen. I tried Your solution and it working fine.

  • August 22, 2003 at 10:49 am

    #471036

    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