Get count of columns having same value in comma separated format Sql

Question

Hi i need a complex query my table structure is

attribute_id value    entity_id 
188         48,51,94     1
188         43,22        2
188         43,22        3
188         43,22        6
190         33,11        10
190         90,61        12
190         90,61        15

I need the count of the value like

attribute_id value    count
188         48         2
188         43         3
188         51         1
188         94         1
188         22         2
190         33         1
190         11         1
190         90         2
190         61         2

I have searched a lot on google to have something like this but unfortunately i didn't get any success. Please suggest me how can i achieve this .

Are the range of possible values known and fixed, or could there be any number of values there? — Tim Biegeleisen, Feb 28 '17 at 07:52
no they are not fixed , i can pass attribute_id value in query only .Please suggest . — Rohit Goel, Feb 28 '17 at 07:53
Never, ever store data as comma separated items! It will only cause you lots of problems. — jarlh, Feb 28 '17 at 07:54
I think the only way to avoid using a UDF for this, would be to somehow use `FIND_IN_SET()`. But this would require knowing what the possible values can be. If this isn't known/fixed, you're probably looking at a very ugly query. In this case, I might recommend another tool for the job. — Tim Biegeleisen, Feb 28 '17 at 07:55
One way to achieve your result is two step: first split the comma value to multiple row, then use a normal COUNT function to get that output. Check this link for first step: http://stackoverflow.com/questions/19073500/sql-split-comma-separated-row — Pham X. Bach, Feb 28 '17 at 08:01
Don't save CSV in a column http://stackoverflow.com/questions/41304945/best-type-of-indexing-when-there-is-like-clause/41305027#41305027 http://stackoverflow.com/questions/41215624/sql-table-with-list-entry-vs-sql-table-with-a-row-for-each-entry/41215681#41215681 Don't save CSV in a column Don't save CSV in a column — e4c5, Feb 28 '17 at 09:11

score 0 · Answer 1 · answered Feb 28 '17 at 08:19

I use a UDF for things like this. If that could work for you:

CREATE FUNCTION [dbo].[UDF_StringDelimiter]
/*********************************************************
**  Takes Parameter "LIST" and transforms it for use    **
**  to select individual values or ranges of values.    **
**                                                      **
**  EX: 'This,is,a,test' = 'This' 'Is' 'A' 'Test'       **
*********************************************************/
    (
          @LIST             VARCHAR(8000)
         ,@DELIMITER        VARCHAR(255)
    )

RETURNS @TABLE TABLE 
    ( 
         [RowID] INT IDENTITY
        ,[Value] VARCHAR(255)
    )
WITH SCHEMABINDING
AS 
BEGIN
    DECLARE 
         @LISTLENGTH AS SMALLINT
        ,@LISTCURSOR AS SMALLINT
        ,@VALUE AS VARCHAR(255)
    ;
    SELECT 
         @LISTLENGTH = LEN(@LIST) - LEN(REPLACE(@LIST,@DELIMITER,'')) + 1
        ,@LISTCURSOR = 1
        ,@VALUE = ''
    ;
    WHILE @LISTCURSOR <= @LISTLENGTH
    BEGIN

        INSERT INTO @TABLE (Value)
        SELECT 
            CASE
                WHEN @LISTCURSOR < @LISTLENGTH
                    THEN SUBSTRING(@LIST,1,PATINDEX('%' + @DELIMITER + '%',@LIST) - 1)
                ELSE SUBSTRING(@LIST,1,LEN(@LIST))
            END
        ;
        SET @LIST = STUFF(@LIST,1,PATINDEX('%' + @DELIMITER + '%',@LIST),'')
        ;       
        SET @LISTCURSOR = @LISTCURSOR + 1
        ;
    END
    ;
    RETURN
    ;
END
;

The UDF takes two parameters: A string to be split, and the delimiter to split by. I've been using it for all sorts of different things over the years, because sometimes you need to split by a comma, sometimes by a space, sometimes by a whole string.

Once you have that UDF, you can just do this:

DECLARE @TABLE TABLE 
(
     Attribute_ID INT
    ,Value VARCHAR(55)
    ,Entity_ID INT
);

INSERT INTO @TABLE VALUES (188, '48,51,94', 1);
INSERT INTO @TABLE VALUES (188, '43,22', 2);
INSERT INTO @TABLE VALUES (188, '43,22', 3);
INSERT INTO @TABLE VALUES (188, '43,22', 6);
INSERT INTO @TABLE VALUES (190, '33,11', 10);
INSERT INTO @TABLE VALUES (190, '90,61', 12);
INSERT INTO @TABLE VALUES (190, '90,61', 15);

SELECT
     T1.Attribute_ID
    ,T2.Value
    ,COUNT(T2.Value) AS Counter
FROM @TABLE T1
CROSS APPLY dbo.UDF_StringDelimiter(T1.Value,',') T2
GROUP BY T1.Attribute_ID,T2.Value
ORDER BY T1.Attribute_ID ASC, Counter DESC
;

I did an ORDER BY Attribute_ID ascending and then the Counter descending so that you get each Attribute_ID with the most common repeating values first. You could change that, of course.

Returns this:

Attribute_ID     Value     Counter
-----------------------------------
188              43            3
188              22            3
188              94            1
188              48            1
188              51            1
190              61            2
190              90            2
190              11            1
190              33            1

Get count of columns having same value in comma separated format Sql

1 Answers1