CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008
bebopayan
bebopayan

Reputation: 45

SQL Count distinct values within the field

I have this weird scenario (at least it is for me) where I have a table (actually a result set, but I want to make it simpler) that looks like the following:

ID  |  Actions
------------------
1   |  10,12,15
2   |  11,12,13
3   |  15  
4   |  14,15,16,17

And I want to count the different actions in the all the table. In this case, I want the result to be 8 (just counting 10, 11, ...., 17; and ignoring the repeated values).

In case it matters, I am using MS SQL 2008.

If it makes it any easier, the Actions were previously on XML that looks like

<root>
  <actions>10,12,15</actions>
</root>

I doubt it makes it easier, but somebody might comeback with an xml function that I am not aware and just makes everything easier.

Let me know if there's something else I should say.

Upvotes: 0

Views: 1531

Answers (4)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Table1
    ([ID] int, [Actions] varchar(11))
;

INSERT INTO Table1
    ([ID], [Actions])
VALUES
    (1, '10,12,15'),
    (2, '11,12,13'),
    (3, '15'),
    (4, '14,15,16,17')
;

Query 1:

DECLARE @S varchar(255)
DECLARE @X xml

SET @S = (SELECT Actions + ',' FROM Table1 FOR XML PATH(''))
SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,',','</s><s>') + '</s></root>')

SELECT count(distinct [Value])
FROM (
SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c)) AS Result
WHERE [Value] > 0

Results:

| COLUMN_0 |
|----------|
|        8 |

EDIT :

I think this is exactly what you are looking for :

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

DECLARE @X xml

SELECT @X = CONVERT(xml,replace('
<root>
  <actions>10,12,15</actions>
  <actions>11,12,13</actions>
  <actions>15</actions>
  <actions>14,15,16,17</actions>
</root>
',',','</actions><actions>'))

SELECT count(distinct [Value])
FROM (
SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/actions') T(c)) AS Result

Results:

| COLUMN_0 |
|----------|
|        8 |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If you have a table of Actions with one row per possible action id, you can do this with a join:

select count(distinct a.ActionId)
from t join
     Actions a
     on ','+t.Actions+',' like '%,'+cast(a.ActionId as varchar(255))+',%';

You could also create a table of numbers (using a CTE) if you know the actions are within some range.

Upvotes: 0

AAzami
AAzami

Reputation: 406

A bit if a mess but here it is Create the function first and then call the lower code. 

/* Helper Function */

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH splitter_cte AS (
      SELECT CHARINDEX(@sep, @s) as pos, 0 as lastPos
      UNION ALL
      SELECT CHARINDEX(@sep, @s, pos + 1), pos
      FROM splitter_cte
      WHERE pos > 0
     )
    SELECT SUBSTRING(@s, lastPos + 1,
                     case when pos = 0 then 80000
                     else pos - lastPos -1 end) as chunk
    FROM splitter_cte
  )
GO
---------------- End of Function 


/* Function Call */ 
Declare @Actions varchar(1000)
SELECT @Actions = STUFF((SELECT ',' + actions
          FROM tblActions
          ORDER BY actions
          FOR XML PATH('')), 1, 1, '')

SELECT  Distinct *
  FROM dbo.Split(',', @Actions)
OPTION(MAXRECURSION 0);

Upvotes: 0

suff trek
suff trek

Reputation: 39767

Using approach similar to http://codecorner.galanter.net/2012/04/25/t-sql-string-deaggregate-split-ungroup-in-sql-server/:

First you need a function that would split string, there're many examples on SO, here's one of them:

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
)

Using this you can run a simple query:

SELECT COUNT(DISTINCT S) FROM MyTable CROSS APPLY dbo.Split(',', Actions)

Here is the demo: http://sqlfiddle.com/#!3/5e706/3/0

Upvotes: 1

Related Questions