0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for amateur radio enthusiasts

---Using SQLMenace's answer to 
-- How Do I Split a Delimited String in SQL Server
-- Without Creating a Function? 
--- http://stackoverflow.com/a/5722748/119477

SELECT Count(ID) kount,
        CASE WHEN GROUPING(OpenCount) = 1 THEN 'ALL'
        ELSE  CAST(OpenCount as varchar(10))
        END [Open Count]
FROM (
  SELECT 
         ph.ID,
         COUNT(t.VALUE) OpenCount
         
         
    FROM
           PostHistory ph
            OUTER APPLY
               ( SELECT 
                  SUBSTRING(',' + text + ',', Number + 1,
                  CHARINDEX(',', ',' + text + ',', Number + 1) - Number -1)AS VALUE
                  FROM master..spt_values 
                  WHERE  Type = 'P'
                     AND Number <= LEN(',' + text + ',') - 1
                     AND SUBSTRING(',' + text + ',', Number, 1) = ','
                     AND ph.postHistoryTypeID =11) t
    WHERE 
         ph.postHistoryTypeID =11  AND  
         (t.value like '"DisplayName":%' or t.value is null)
    GROUP BY 
    ph.Id) data
GROUP BY
      OpenCount WITH ROLLUP
ORDER BY 
CASE WHEN GROUPING(OpenCount) = 1 then 1 else 0 END ,
  OpenCount

fork query

edited mar 1 12

created mar 1 12 conradfrix