Results 1 to 6 of 6

Thread: group by....

  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: group by....

    id name
    12 tom
    12 rick
    12 mark
    15 jack
    15 rock
    16 kent
    16 mint

    I have the data in the above format

    I want that in the following format

    id name
    12 tom,rick,mark
    15 jack,rock
    16 kent,mint

    How can i do that?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your best bet (by a long shot) is to do this kind of processing on the client. It can make much better decisions about what it can cope with, and can do so MUCH more efficiently than the server can do it.

    With that being said, you can do this kind of processing like:
    PHP Code:
    CREATE TABLE #phred (
       
    id        INT        NOT NULL
    ,  name        VARCHAR(20)    NOT NULL
       
    )

    INSERT INTO #phred
       
    SELECT           12'tom'
       
    UNION ALL SELECT 12'rick'
       
    UNION ALL SELECT 12'mark'
       
    UNION ALL SELECT 15'jack'
       
    UNION ALL SELECT 15'rock'
       
    UNION ALL SELECT 16'kent'
       
    UNION ALL SELECT 16'mint'
       
    UNION ALL SELECT 99'phred'

    SELECT a.idMin(a.name
    +  
    Coalesce(', ' Min(b.name), ''
    +  
    Coalesce(', ' Min(c.name), '')
       
    FROM #phred AS a
       
    LEFT JOIN #phred AS b
          
    ON (b.id a.id
          
    AND a.name b.name)
       
    LEFT JOIN #phred AS c
          
    ON (c.id a.id
          
    AND b.name c.name)
       
    GROUP BY a.id

    DROP TABLE 
    #phred 
    -PatP

  3. #3
    Join Date
    Apr 2003
    Posts
    176
    This worked fine if i have 3 names for one id,What happens if i have 30,40 names against 1 id.
    Should i write 30,40 joins in that case?
    can anyone help in this...
    Thanks.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by bruce_Reid
    This worked fine if i have 3 names for one id,What happens if i have 30,40 names against 1 id.
    Should i write 30,40 joins in that case?
    can anyone help in this...
    Thanks.
    You have to do this on client side...

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by bruce_Reid
    This worked fine if i have 3 names for one id,What happens if i have 30,40 names against 1 id.
    Should i write 30,40 joins in that case?
    can anyone help in this...
    Thanks.
    That's why I started my post with "Your best bet (by a long shot) is to do this kind of processing on the client." This kind of processing is easily handled on a client... It can be done on the server when you must, but it isn't a good idea if you can avoid it.

    -PatP

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    set nocount on
    if object_id('dbo.t2') is not null drop table dbo.t2
    create table t2 (id int, name varchar(25))
    go
    insert t2 values (12, 'tom')
    insert t2 values (12, 'rick')
    insert t2 values (12, 'mark')
    insert t2 values (15, 'jack')
    insert t2 values (15, 'rock')
    insert t2 values (16, 'kent')
    insert t2 values (16, 'mint')
    go
    declare @str varchar(8000), @id int
    declare @tbl table ([id] int, [name] varchar(25))
    select @id = min([id]) from t2
    while @id is not null begin
       set @str = ''
       select @str = @str + case when @str = '' then '' else ', ' end + name from t2 where id = @id
       insert @tbl select @id, @str
       select @id = min([id]) from t2 where [id] > @id
    end
    select * from @tbl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •