none
(Sumber: milist SQL Server) SQL Command RRS feed

  • Pertanyaan

  • Halo All,

    Bila saya punya table seperti ini:

    CustNo

    OrderNo

    0001

    A001

    0001

    A002

    0002

    A003

    0002

    A004

    0002

    A005

    0003

    A006

    0003

    A007


    Lalu saya ingin hasil query seperti ini:

    CustNo

    OrderNos

    0001

    A001,A002

    0002

    A003,A004,A005

    0003

    A006,A007


    Bagaimana ya SQL commandnya bila tidak memakai cursor.

    Thanks sebelumnya, mohon maaf bila sudah pernah ditanyakan.



    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Senin, 12 Desember 2011 09.09
    Moderator

Jawaban

  • Kalau SQL 2005/2008 bisa pake XML Path:

    SELECT
       t1.CustNo,
       OrderList = substring((SELECT ( ', ' + OrderNo )
                               FROM dbo.CustOrder t2
                               WHERE t1.CustNo = t2.CustNo
                               ORDER BY
                                  CustNo,
                                  OrderNo
                               FOR XML PATH( '' )
                              ), 3, 1000 )FROM dbo.CustOrder t1
    GROUP BY CustNo


    Dijawab oleh: Elvin


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Senin, 12 Desember 2011 09.09
    Moderator
  • Gimana kalo pake begini

    create function getorders (@customercode) varchar(8000)

    as

    begin

            declare @temp varchar(8000)=''

            select @temp = @temp +';'+ orderno

            from order where customercode = @customercode  

            return @temp

    end

    go

     

    select customercode, getorders (@customercode)

    from customer 

     

    cara saya punya kelemahan :

    1. tidak bisa lebih dari 8000 karakter

    Dijawab oleh: Johan Max


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Rabu, 14 Desember 2011 04.01
    Moderator

Semua Balasan

  • Kalau SQL 2005/2008 bisa pake XML Path:

    SELECT
       t1.CustNo,
       OrderList = substring((SELECT ( ', ' + OrderNo )
                               FROM dbo.CustOrder t2
                               WHERE t1.CustNo = t2.CustNo
                               ORDER BY
                                  CustNo,
                                  OrderNo
                               FOR XML PATH( '' )
                              ), 3, 1000 )FROM dbo.CustOrder t1
    GROUP BY CustNo


    Dijawab oleh: Elvin


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Senin, 12 Desember 2011 09.09
    Moderator
  • Gimana kalo pake begini

    create function getorders (@customercode) varchar(8000)

    as

    begin

            declare @temp varchar(8000)=''

            select @temp = @temp +';'+ orderno

            from order where customercode = @customercode  

            return @temp

    end

    go

     

    select customercode, getorders (@customercode)

    from customer 

     

    cara saya punya kelemahan :

    1. tidak bisa lebih dari 8000 karakter

    Dijawab oleh: Johan Max


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Rabu, 14 Desember 2011 04.01
    Moderator
  • Hi Elvin and Johan,

     

    Thank you, both solutions work!

    Dan isi kolomnya tidak mgkn lebih dari 1000 atau 8000 chars kok :-)
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Rabu, 14 Desember 2011 04.03
    Moderator