SQL: Combining Rows with Some Conditions And Delete Rest of Them

I have a table like this;

Table: PROS开发者_如何学GoPECT

ID CUSTOMER NUMBER
1  A        XXXA1
1  B        XXXA1
1  G        XXX00
2  C        XXX32
2  D        XXX32
2  E        XXX32
2  F        XXX66    

I want to combine rows' customer's name with ; where they has same ID and NUMBER. (and delete other rows.)

I want to it like this;

Table: PROSPECT

    ID CUSTOMER     NUMBER
    1  A;B          XXXA1
    1  G            XXX00
    2  C;D;E        XXX32
    2  F            XXX66

How can I do that?

---

Disclaimer

I agree with @Dems that you should not change your normalized data to this. A better alternative might be to create a VIEW that presents you the data in the format you need.

SQL Statement

CREATE VIEW dbo.VIEW_PROSPECT AS 
  SELECT  po.ID
          , STUFF((SELECT '; ' + CUSTOMER FROM PROSPECT pi WHERE pi.ID = po.ID AND pi.N  UMBER = po.NUMBER ORDER BY CUSTOMER FOR XML PATH('')),1,2,'') AS CUSTOMER
          , po.NUMBER
  FROM    PROSPECT po
  GROUP BY
          po.ID
          , po.NUMBER

Test script

;WITH PROSPECT(ID, CUSTOMER, NUMBER) AS (
  SELECT 1, 'A', 'XXXA1'
  UNION ALL SELECT 1, 'B', 'XXXA1'
  UNION ALL SELECT 1, 'G', 'XXX00'
  UNION ALL SELECT 2, 'C', 'XXX32'
  UNION ALL SELECT 2, 'D', 'XXX32'
  UNION ALL SELECT 2, 'E', 'XXX32'
  UNION ALL SELECT 2, 'F', 'XXX66'
)
SELECT  po.ID
        , STUFF((SELECT '; ' + CUSTOMER FROM PROSPECT pi WHERE pi.ID = po.ID AND pi.NUMBER = po.NUMBER ORDER BY CUSTOMER FOR XML PATH('')),1,2,'') AS CUSTOMER
        , po.NUMBER
FROM    PROSPECT po
GROUP BY
        po.ID
        , po.NUMBER
ORDER BY
        ID
        , CUSTOMER
        , NUMBER        

---

SELECT DISTINCT ID, LEFT(T.CUSTOMER,LEN(T.CUSTOMER)-1), NUMBER
                     FROM PROSPECT A CROSS APPLY 
                            (SELECT CUSTOMER + ';' AS [text()] FROM PROSPECT B 
                                        WHERE A.ID = B.ID and A.NUMBER = B.NUMBER
                             ORDER BY ID FOR XML PATH('')) T (CUSTOMER)