Questa funziona sicuramente su MS SQL Server.
Fa il conteggio degli ingredienti fino a 6, ma espandendola a 20 (io non
me la sono sentita :)) li prenderebbe tutti.
select D.Ingrediente, COUNT(D.ID) as Conteggio
from (
select C.ID, cast(C.Ingrediente1 as varchar) as Ingrediente
from (
select B.ID,
case when not B.IDX1 is null then SUBSTRING(B.Ingredienti, 1,
B.IDX1) else B.Ingredienti end AS Ingrediente1,
case when not B.IDX1 is null then case when not B.IDX2 is null
then SUBSTRING(B.Ingredienti, B.IDX1, B.IDX2 - B.IDX1) else
SUBSTRING(B.Ingredienti, B.IDX1, 1000000) end else null end AS
Ingrediente2,
case when not B.IDX2 is null then case when not B.IDX3 is null
then SUBSTRING(B.Ingredienti, B.IDX2, B.IDX3 - B.IDX2) else
SUBSTRING(B.Ingredienti, B.IDX2, 1000000) end else null end AS
Ingrediente3,
case when not B.IDX3 is null then case when not B.IDX4 is null
then SUBSTRING(B.Ingredienti, B.IDX3, B.IDX4 - B.IDX3) else
SUBSTRING(B.Ingredienti, B.IDX3, 1000000) end else null end AS
Ingrediente4,
case when not B.IDX4 is null then case when not B.IDX5 is null
then SUBSTRING(B.Ingredienti, B.IDX4, B.IDX5 - B.IDX4) else
SUBSTRING(B.Ingredienti, B.IDX4, 1000000) end else null end AS
Ingrediente5,
case when not B.IDX5 is null then case when not B.IDX6 is null
then SUBSTRING(B.Ingredienti, B.IDX5, B.IDX6 - B.IDX5) else
SUBSTRING(B.Ingredienti, B.IDX5, 1000000) end else null end AS Ingrediente6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX5 + 1) >
0 then CHARINDEX(' ', Ingredienti, IDX5 + 1) else null end as IDX6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX4 +
1) > 0 then CHARINDEX(' ', Ingredienti, IDX4 + 1) else null end as IDX5
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX3 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX3 + 1) else null end
as IDX4
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX2 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX2 + 1) else null end
as IDX3
from (
select *, case when CHARINDEX(' ',
Ingredienti, IDX1 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX1 + 1)
else null end as IDX2
from (
select ID, Ingredienti, case when
CHARINDEX(' ', Ingredienti, 1) > 0 then CHARINDEX(' ', Ingredienti, 1)
else null end as IDX1 from @tab
) as A
) as A
) as A
) as A
) as A
) as B
) as C
union
select C.ID, cast(C.Ingrediente2 as varchar) as Ingrediente
from (
select B.ID,
case when not B.IDX1 is null then SUBSTRING(B.Ingredienti, 1,
B.IDX1) else B.Ingredienti end AS Ingrediente1,
case when not B.IDX1 is null then case when not B.IDX2 is null
then SUBSTRING(B.Ingredienti, B.IDX1, B.IDX2 - B.IDX1) else
SUBSTRING(B.Ingredienti, B.IDX1, 1000000) end else null end AS
Ingrediente2,
case when not B.IDX2 is null then case when not B.IDX3 is null
then SUBSTRING(B.Ingredienti, B.IDX2, B.IDX3 - B.IDX2) else
SUBSTRING(B.Ingredienti, B.IDX2, 1000000) end else null end AS
Ingrediente3,
case when not B.IDX3 is null then case when not B.IDX4 is null
then SUBSTRING(B.Ingredienti, B.IDX3, B.IDX4 - B.IDX3) else
SUBSTRING(B.Ingredienti, B.IDX3, 1000000) end else null end AS
Ingrediente4,
case when not B.IDX4 is null then case when not B.IDX5 is null
then SUBSTRING(B.Ingredienti, B.IDX4, B.IDX5 - B.IDX4) else
SUBSTRING(B.Ingredienti, B.IDX4, 1000000) end else null end AS
Ingrediente5,
case when not B.IDX5 is null then case when not B.IDX6 is null
then SUBSTRING(B.Ingredienti, B.IDX5, B.IDX6 - B.IDX5) else
SUBSTRING(B.Ingredienti, B.IDX5, 1000000) end else null end AS Ingrediente6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX5 + 1) >
0 then CHARINDEX(' ', Ingredienti, IDX5 + 1) else null end as IDX6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX4 +
1) > 0 then CHARINDEX(' ', Ingredienti, IDX4 + 1) else null end as IDX5
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX3 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX3 + 1) else null end
as IDX4
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX2 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX2 + 1) else null end
as IDX3
from (
select *, case when CHARINDEX(' ',
Ingredienti, IDX1 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX1 + 1)
else null end as IDX2
from (
select ID, Ingredienti, case when
CHARINDEX(' ', Ingredienti, 1) > 0 then CHARINDEX(' ', Ingredienti, 1)
else null end as IDX1 from @tab
) as A
) as A
) as A
) as A
) as A
) as B
) as C
union
select C.ID, cast(C.Ingrediente3 as varchar) as Ingrediente
from (
select B.ID,
case when not B.IDX1 is null then SUBSTRING(B.Ingredienti, 1,
B.IDX1) else B.Ingredienti end AS Ingrediente1,
case when not B.IDX1 is null then case when not B.IDX2 is null
then SUBSTRING(B.Ingredienti, B.IDX1, B.IDX2 - B.IDX1) else
SUBSTRING(B.Ingredienti, B.IDX1, 1000000) end else null end AS
Ingrediente2,
case when not B.IDX2 is null then case when not B.IDX3 is null
then SUBSTRING(B.Ingredienti, B.IDX2, B.IDX3 - B.IDX2) else
SUBSTRING(B.Ingredienti, B.IDX2, 1000000) end else null end AS
Ingrediente3,
case when not B.IDX3 is null then case when not B.IDX4 is null
then SUBSTRING(B.Ingredienti, B.IDX3, B.IDX4 - B.IDX3) else
SUBSTRING(B.Ingredienti, B.IDX3, 1000000) end else null end AS
Ingrediente4,
case when not B.IDX4 is null then case when not B.IDX5 is null
then SUBSTRING(B.Ingredienti, B.IDX4, B.IDX5 - B.IDX4) else
SUBSTRING(B.Ingredienti, B.IDX4, 1000000) end else null end AS
Ingrediente5,
case when not B.IDX5 is null then case when not B.IDX6 is null
then SUBSTRING(B.Ingredienti, B.IDX5, B.IDX6 - B.IDX5) else
SUBSTRING(B.Ingredienti, B.IDX5, 1000000) end else null end AS Ingrediente6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX5 + 1) >
0 then CHARINDEX(' ', Ingredienti, IDX5 + 1) else null end as IDX6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX4 +
1) > 0 then CHARINDEX(' ', Ingredienti, IDX4 + 1) else null end as IDX5
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX3 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX3 + 1) else null end
as IDX4
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX2 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX2 + 1) else null end
as IDX3
from (
select *, case when CHARINDEX(' ',
Ingredienti, IDX1 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX1 + 1)
else null end as IDX2
from (
select ID, Ingredienti, case when
CHARINDEX(' ', Ingredienti, 1) > 0 then CHARINDEX(' ', Ingredienti, 1)
else null end as IDX1 from @tab
) as A
) as A
) as A
) as A
) as A
) as B
) as C
union
select C.ID, cast(C.Ingrediente4 as varchar) as Ingrediente
from (
select B.ID,
case when not B.IDX1 is null then SUBSTRING(B.Ingredienti, 1,
B.IDX1) else B.Ingredienti end AS Ingrediente1,
case when not B.IDX1 is null then case when not B.IDX2 is null
then SUBSTRING(B.Ingredienti, B.IDX1, B.IDX2 - B.IDX1) else
SUBSTRING(B.Ingredienti, B.IDX1, 1000000) end else null end AS
Ingrediente2,
case when not B.IDX2 is null then case when not B.IDX3 is null
then SUBSTRING(B.Ingredienti, B.IDX2, B.IDX3 - B.IDX2) else
SUBSTRING(B.Ingredienti, B.IDX2, 1000000) end else null end AS
Ingrediente3,
case when not B.IDX3 is null then case when not B.IDX4 is null
then SUBSTRING(B.Ingredienti, B.IDX3, B.IDX4 - B.IDX3) else
SUBSTRING(B.Ingredienti, B.IDX3, 1000000) end else null end AS
Ingrediente4,
case when not B.IDX4 is null then case when not B.IDX5 is null
then SUBSTRING(B.Ingredienti, B.IDX4, B.IDX5 - B.IDX4) else
SUBSTRING(B.Ingredienti, B.IDX4, 1000000) end else null end AS
Ingrediente5,
case when not B.IDX5 is null then case when not B.IDX6 is null
then SUBSTRING(B.Ingredienti, B.IDX5, B.IDX6 - B.IDX5) else
SUBSTRING(B.Ingredienti, B.IDX5, 1000000) end else null end AS Ingrediente6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX5 + 1) >
0 then CHARINDEX(' ', Ingredienti, IDX5 + 1) else null end as IDX6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX4 +
1) > 0 then CHARINDEX(' ', Ingredienti, IDX4 + 1) else null end as IDX5
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX3 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX3 + 1) else null end
as IDX4
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX2 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX2 + 1) else null end
as IDX3
from (
select *, case when CHARINDEX(' ',
Ingredienti, IDX1 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX1 + 1)
else null end as IDX2
from (
select ID, Ingredienti, case when
CHARINDEX(' ', Ingredienti, 1) > 0 then CHARINDEX(' ', Ingredienti, 1)
else null end as IDX1 from @tab
) as A
) as A
) as A
) as A
) as A
) as B
) as C
union
select C.ID, cast(C.Ingrediente5 as varchar) as Ingrediente
from (
select B.ID,
case when not B.IDX1 is null then SUBSTRING(B.Ingredienti, 1,
B.IDX1) else B.Ingredienti end AS Ingrediente1,
case when not B.IDX1 is null then case when not B.IDX2 is null
then SUBSTRING(B.Ingredienti, B.IDX1, B.IDX2 - B.IDX1) else
SUBSTRING(B.Ingredienti, B.IDX1, 1000000) end else null end AS
Ingrediente2,
case when not B.IDX2 is null then case when not B.IDX3 is null
then SUBSTRING(B.Ingredienti, B.IDX2, B.IDX3 - B.IDX2) else
SUBSTRING(B.Ingredienti, B.IDX2, 1000000) end else null end AS
Ingrediente3,
case when not B.IDX3 is null then case when not B.IDX4 is null
then SUBSTRING(B.Ingredienti, B.IDX3, B.IDX4 - B.IDX3) else
SUBSTRING(B.Ingredienti, B.IDX3, 1000000) end else null end AS
Ingrediente4,
case when not B.IDX4 is null then case when not B.IDX5 is null
then SUBSTRING(B.Ingredienti, B.IDX4, B.IDX5 - B.IDX4) else
SUBSTRING(B.Ingredienti, B.IDX4, 1000000) end else null end AS
Ingrediente5,
case when not B.IDX5 is null then case when not B.IDX6 is null
then SUBSTRING(B.Ingredienti, B.IDX5, B.IDX6 - B.IDX5) else
SUBSTRING(B.Ingredienti, B.IDX5, 1000000) end else null end AS Ingrediente6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX5 + 1) >
0 then CHARINDEX(' ', Ingredienti, IDX5 + 1) else null end as IDX6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX4 +
1) > 0 then CHARINDEX(' ', Ingredienti, IDX4 + 1) else null end as IDX5
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX3 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX3 + 1) else null end
as IDX4
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX2 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX2 + 1) else null end
as IDX3
from (
select *, case when CHARINDEX(' ',
Ingredienti, IDX1 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX1 + 1)
else null end as IDX2
from (
select ID, Ingredienti, case when
CHARINDEX(' ', Ingredienti, 1) > 0 then CHARINDEX(' ', Ingredienti, 1)
else null end as IDX1 from @tab
) as A
) as A
) as A
) as A
) as A
) as B
) as C
union
select C.ID, cast(C.Ingrediente6 as varchar) as Ingrediente
from (
select B.ID,
case when not B.IDX1 is null then SUBSTRING(B.Ingredienti, 1,
B.IDX1) else B.Ingredienti end AS Ingrediente1,
case when not B.IDX1 is null then case when not B.IDX2 is null
then SUBSTRING(B.Ingredienti, B.IDX1, B.IDX2 - B.IDX1) else
SUBSTRING(B.Ingredienti, B.IDX1, 1000000) end else null end AS
Ingrediente2,
case when not B.IDX2 is null then case when not B.IDX3 is null
then SUBSTRING(B.Ingredienti, B.IDX2, B.IDX3 - B.IDX2) else
SUBSTRING(B.Ingredienti, B.IDX2, 1000000) end else null end AS
Ingrediente3,
case when not B.IDX3 is null then case when not B.IDX4 is null
then SUBSTRING(B.Ingredienti, B.IDX3, B.IDX4 - B.IDX3) else
SUBSTRING(B.Ingredienti, B.IDX3, 1000000) end else null end AS
Ingrediente4,
case when not B.IDX4 is null then case when not B.IDX5 is null
then SUBSTRING(B.Ingredienti, B.IDX4, B.IDX5 - B.IDX4) else
SUBSTRING(B.Ingredienti, B.IDX4, 1000000) end else null end AS
Ingrediente5,
case when not B.IDX5 is null then case when not B.IDX6 is null
then SUBSTRING(B.Ingredienti, B.IDX5, B.IDX6 - B.IDX5) else
SUBSTRING(B.Ingredienti, B.IDX5, 1000000) end else null end AS Ingrediente6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX5 + 1) >
0 then CHARINDEX(' ', Ingredienti, IDX5 + 1) else null end as IDX6
from (
select *, case when CHARINDEX(' ', Ingredienti, IDX4 +
1) > 0 then CHARINDEX(' ', Ingredienti, IDX4 + 1) else null end as IDX5
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX3 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX3 + 1) else null end
as IDX4
from (
select *, case when CHARINDEX(' ', Ingredienti,
IDX2 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX2 + 1) else null end
as IDX3
from (
select *, case when CHARINDEX(' ',
Ingredienti, IDX1 + 1) > 0 then CHARINDEX(' ', Ingredienti, IDX1 + 1)
else null end as IDX2
from (
select ID, Ingredienti, case when
CHARINDEX(' ', Ingredienti, 1) > 0 then CHARINDEX(' ', Ingredienti, 1)
else null end as IDX1 from @tab
) as A
) as A
) as A
) as A
) as A
) as B
) as C
) as D
where D.Ingrediente is not null
group by D.Ingrediente
order by D.Ingrediente
Il 15/06/2023 13:48, Leonardo Boselli ha scritto:
Ho un database con colonne ID[integer], Ingredienti[text],
commensali[float], data[datetime].
Il campo ingredientoi contiene una lista (tra 1 e 20 valori, separati
da spazio) degli ingredienti presenti per oltre il 5% del menu.
Domadona: c'è un modo di estrarre una tabella che mi dia "ingrediente"
e "frequenza" ossia ogni ingrediente in quante cene è statao presente ?
(ovviamante un group by ingredienti non funziona in quanto un rtecord
potrebbe avere «pasta pomodoro fungo maiale mela» un altre «riso fungo
prezzemolo maiale» e quindi in uscita dovrei avere maiale:2 fungo:2
pasta:1 pomodoro:1 ...)
C'è un modo diretto restando in una query sql ?
--
Leonardo Boselli
Firenze, Toscana, Europa
