Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Posts
    30

    Unanswered: How to group varchar columns

    Hi.. sorry, got another problem

    I need to change the table below:
    StuffA, 'Supplier1'
    StuffA, 'Supplier2'
    StuffA, 'Supplier3'
    StuffB, 'Supplier4'
    StuffB, 'Supplier1'

    into :

    StuffA, 'Supplier1, Supplier2, Supplier3'
    StuffB, 'Supplier4, Supplier1'

    Using SQL command.. Can I build some function or is there SQL command to do this?? Thx...
    Last edited by prd00; 01-28-03 at 08:31.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's called "denormalizing"

    there is no sql command to do it

    write a stored proc with a cursor that loops through a sorted result set


    rudy

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    It's called a crosstab.
    You can create a genral method of doing this using dynamic sql.
    Do a search for crosstab on www.sqlteam.com.

    if you have a max number of row entries you can
    select name,
    (select top1 t1.supplier from tbl t1 where t1.name = tbl.name order by t1.supplier asc) ,
    (select top1 t1.supplier from (select top 2 t2.supplier from tbl t2 where t2.name = tbl.name order by t2.supplier asc) as t2 order by t2.supplier desc ,
    (select top1 t1.supplier from (select top 3 t2.supplier from tbl t2 where t2.name = tbl.name order by t2.supplier asc) as t2 order by t2.supplier desc
    from tbl
    group by name

Posting Permissions

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