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
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)