SQL group by splitted data

user_7430795 0
Viewed 48

I have a table that looks like this:

   Name  |      Temperament
----------------------------------------
 "Husky" | "Smart, Loyal, Cute"
 "Poodle"| "Smart, Cute"
 "Golden"| "Cute, Loyal, Caring, Loving"

And I want to project this data as a group by of the temperaments. For example:

Temperament |             Name            | Count(Optional)
-----------------------------------------------------------   
"Smart"     | "Poodle", "Husky"           | 2
"Loyal"     | "Husky", "Golden"           | 2
"Cute"      | "Poodle", "Golden", "Husky" | 3
"Caring"    | "Golden"                    | 1
"Loving"    | "Golden"                    | 1

My problem is that I couldn't find a way to split the string in my table and manipulate this data. It would be great if anyone can help me with this problem.

If pure SQL can't be done it might be helpful to tell that I'm using Entity Framework and if the solution can be written in it, it might be even better.

Thank you all.

recursive-query csv entity-framework sql .net
2 Answers

This can be done in pure SQL. In Oracle, you can use regexp functions and a regular expression to split the delimited strings, then use string aggregation to generate the list of names per temperament:

with cte (name, temperament, temp, cnt, lvl) as (
    select 
        name, 
        temperament, 
        regexp_substr (temperament, '[^, ]+', 1, 1) temp, 
        regexp_count(temperament, ',') cnt,
        1 lvl 
    from mytable
    union all
    select 
        name, 
        temperament, 
        regexp_substr (temperament, '[^, ]+', 1, lvl  + 1), 
        cnt,
        lvl + 1
    from cte
    where lvl <= cnt
)
select 
    temp temperament, 
    listagg(name, ', ') within group(order by name) name, 
    count(*) cnt
from cte
group by temp
order by 1

Demo on DB Fiddle:

TEMPERAMENT | NAME                  | CNT
:---------- | :-------------------- | --:
Caring      | Golden                |   1
Cute        | Golden, Husky, Poodle |   3
Loving      | Golden                |   1
Loyal       | Golden, Husky         |   2
Smart       | Husky, Poodle         |   2
user_106767160

If anyone needs the answer:

var result = (from t in ((from t1 in db.mytables select new {tmp= t1.TEMP1}).Concat(from t2 in db.mytables select new {tmp= t2.TEMP2}).Concat(from t3 in db.mytables select new {tmp= t3.TEMP3})) group t.tmp by t.tmp into g select new { tmp = g.Key, cnx=g.Count()}).ToList();

Hope it'll help someone!

user_74307950
Related