-1

I have a table as shown below

Data1

Delta,Charlie
Delta,Bravo
Bravo,Charlie
Tango,Bravo
Alpha

I want to get a count of the words.

So I want to get something like

Delta 2
Charlie 2
Bravo 3
Tango 1
Alpha 1

My idea is to create a temp table and split the strings based on, character and keep inserting it in the temp table based on a function defined here: T-SQL split string and then group them and count them. Is there a better way?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SP1
  • 1,182
  • 3
  • 22
  • 47

1 Answers1

3

no need for a temp table, you can do it in one run.

see this example : 

DECLARE 
    @tb TABLE (data1 VARCHAR(500) ) 

INSERT INTO @tb VALUES 
('Delta,Charlie'),
('Delta,Bravo'),
('Bravo,Charlie'),
('Tango,Bravo'),
('Alpha')

SELECT data1, COUNT(*) total
FROM (
SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) data1
FROM (
    SELECT CAST('<XMLRoot><RowData>' + REPLACE(data1,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) split
    FROM @tb
) D
CROSS APPLY split.nodes('/XMLRoot/RowData')m(n)
) D 
GROUP BY data1

More Explanation (per scsimon requested)

The method above is one of approaches that splits strings using XML. Basically what we did is replacing the commas from the actual data with XML tags </RowData><RowData> that defines each split, then concat that with another XML tags <XMLRoot><RowData></RowData></XMLRoot> that define each row.

So, if we get the first row for example Delta,Charlie it'll be converted into : 

<XMLRoot>
    <RowData>Delta</RowData>
    <RowData>Charlie</RowData>
</XMLRoot>

if you know how XML structure works or at least HTML (both have their similarities) , then you'll get the idea.

Then, we used XML techniques that SQL Server already supports, and selected the nodes (rows) , which then converted back to VARCHAR to be treated as single value (regular row).

This is a quick explanation, while there are a lot of other methods some of them also uses XML, it depends on your preferences.

if you need other techniques, you can check this out , which covers almost all other techniques out there. (A good article too).

If you're using SQL Server 2016 or above, you can use STRING_SPLIT function, which will give you the same results.

iSR5
  • 3,274
  • 2
  • 14
  • 13
  • Whoopss..pretty cool and much better than my simple idea...really appreciate your time. – SP1 Jan 16 '19 at 02:07
  • This is pretty complicated so an explanation would really pump your answer up – S3S Jan 16 '19 at 02:46
  • @scsimon which part needs explanation ? it's simple approach using XML method to split strings. – iSR5 Jan 16 '19 at 02:51
  • You don’t see much xml in the wild (in sql server) so many people won’t understand how this works. Just my two cents. I can only name two people that use it much in answering question on SO too. – S3S Jan 16 '19 at 03:03
  • @scsimon I hope this update will give a clear explanation – iSR5 Jan 16 '19 at 03:34
  • Very well done – S3S Jan 16 '19 at 03:36