CODEWITHSUNDEEP
sql-server-2008-r2
Zerotoinfinity
Zerotoinfinity

Reputation: 6540

Removing value from the comma seperated string

I have a table which has some duplicate rows, I am able to find duplicate out of them which looks like this

CREATE TABLE Holder
(
    HPKEY INT,
    HSOMEVALUE VARCHAR(50)
);

INSERT INTO HOLDER (HPKEY,HSOMEVALUE)
VALUES
(1,'abc'),
(2,'abc'),
(5,'abc'),
(7,'xyz'),
(9,'xyz')

and now I have to get the result set into this format

Somvalue         MINKEY           OTHER_KEYS_EXCEPT_MIN
abc               1               2,5
xyz               7                9

but I am getting all the keys in third column .

I have created SQLFIDDLER for the same.

Upvotes: 0

Views: 51

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280645

DECLARE @Holder TABLE
(
    HPKEY INT,
    HSOMEVALUE VARCHAR(50)
);

INSERT INTO @HOLDER (HPKEY,HSOMEVALUE)
VALUES
(1,'abc'),
(2,'abc'),
(5,'abc'),
(7,'xyz'),
(9,'xyz');

SELECT SomeValue = HSOMEVALUE, MINKEY, 
  OTHER_KEYS_EXCEPT_MIN = STUFF(
    (SELECT ',' + CONVERT(varchar(11), h2.HPKEY)
       FROM @Holder AS h2 
       WHERE h2.HSOMEVALUE = h.HSOMEVALUE
       AND h2.HPKEY > h.MINKEY
       FOR XML PATH(''), 
       TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 1, N'')
  FROM 
  (
    SELECT HSOMEVALUE, MINKEY = MIN(HPKEY)
    FROM @Holder GROUP BY HSOMEVALUE
  ) AS h
  ORDER BY SomeValue;

On SQL Server 2017+, this is a lot less messy, IMHO, with STRING_AGG():

SELECT SomeValue = h.HSOMEVALUE, h.MINKEY, 
    OTHER_KEYS_EXCEPT_MIN = STRING_AGG(h2.HPKEY, ',')
  FROM @holder AS h2
  INNER JOIN 
  (
    SELECT HSOMEVALUE, MINKEY = MIN(HPKEY)
    FROM @Holder GROUP BY HSOMEVALUE
  ) AS h 
  ON h.HSOMEVALUE = h2.HSOMEVALUE
  WHERE h2.HPKEY > h.MINKEY
  GROUP BY h.HSOMEVALUE, h.MINKEY
  ORDER BY SomeValue;

Upvotes: 1

Related Questions