CODEWITHSUNDEEP
sqlsql-serverdatabaserow
Soner Gönül
Soner Gönül

Reputation: 98750

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

I have a table like this;

Table: PROSPECT

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?

Upvotes: 0

Views: 493

Answers (2)

NaveenBhat
NaveenBhat

Reputation: 3318

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)

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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

Upvotes: 1

Related Questions