cvetygeorg
BAN USERI propose my solution:
CREATE TABLE Lists
(ListID int, ListItem char(1));
Function that split the comma separated items and returns them as table rows.
CREATE FUNCTION Split_fn
( @string varchar(MAX), @delimiter char(1) )
RETURNS @restable TABLE (items varchar(MAX))
AS
BEGIN
DECLARE @idx int, @slice varchar(8000)
SELECT @idx = 1
IF len(@string)<1 OR @string IS NULL RETURN
WHILE @idx!= 0
BEGIN
SET @idx = CHARINDEX(@delimiter, @string)
IF @idx!=0
SET @slice = LEFT(@string, @idx - 1)
ELSE
SET @slice = @string
IF (LEN(@slice)>0)
INSERT INTO @restable(items) VALUES (@slice)
SET @String = RIGHT(@string, LEN(@String) - @idx)
IF LEN(@string) = 0 BREAK
END
RETURN
END;
Procedure that calls the function and adds the items into a table as separate rows with identifier.
CREATE PROCEDURE InsertListItems_sp
( @ListID int, @items varchar(MAX)=NULL )
AS
BEGIN
INSERT INTO Lists(ListID, ListItem)
SELECT @ListID, items
FROM Split_fn (@items, ',')
END;
Execution of the procedure:
EXEC InsertListItems_sp 1, 'A,B,C,A,B'
EXEC InsertListItems_sp 2, 'A,B,A,A,A'
EXEC InsertListItems_sp 3, 'C,D,C'
Query that counts occurrences of items and returns those that are repeated more than once.
SELECT ListID, ListItem, COUNT(*) AS CountOfItem
FROM Lists
GROUP BY ListID, ListItem
HAVING COUNT(*) > 1
ORDER BY ListID ASC
Finally, the query that sums duplicates by identifiers.
SELECT ListID, SUM(CountOfItem) AS SumOfDuplicates
FROM
(SELECT ListID, ListItem, COUNT(*)-1 AS CountOfItem
FROM Lists
GROUP BY ListID, ListItem
HAVING COUNT(*) > 1) AS s
GROUP BY ListID
ORDER BY ListID ASC
The result is:
ListID SumOfDuplicates
----------- ---------------
1 2
2 3
3 1
Another way for creating function Split_fn is:
- cvetygeorg August 20, 2014