Home » Numbers with Odd Frequencies

Numbers with Odd Frequencies

List the numbers which occur odd number of times

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 89
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Numbers with Odd Frequencies with Power Query

Power Query solution 1 for Numbers with Odd Frequencies, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Rs = Table.AddColumn(
    Source, 
    "Answer", 
    each 
      let
        t = List.Transform(
          List.Sort(List.Transform(Text.Split([Numbers], ", "), Number.From)), 
          Text.From
        )
      in
        Text.Combine(
          List.Select(List.Distinct(t), (u) => Number.IsOdd(List.Count(List.Select(t, each _ = u)))), 
          ", "
        )
  )
in
  Rs
Power Query solution 2 for Numbers with Odd Frequencies, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Solucion = Table.AddColumn(
    Source, 
    "Custom", 
    each 
      let
        a = Text.Split([Numbers], ", "), 
        b = Table.FromList(a), 
        c = Table.Group(b, {"Column1"}, {{"Count", each Table.RowCount(_)}}), 
        d = Table.Sort(
          Table.AddColumn(c, "Answer Expected", each Number.IsOdd([Count])), 
          {{"Column1", Order.Ascending}}
        ), 
        e = Text.Combine(Table.SelectRows(d, each [Answer Expected] = true)[Column1], ", ")
      in
        e
  )
in
  Solucion
Power Query solution 3 for Numbers with Odd Frequencies, proposed by Luan Rodrigues: 
let
  Fonte = Data, 
  a = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each try List.Transform(Text.Split([Numbers], ","), Number.From) otherwise null
  ), 
  b = Table.ExpandListColumn(a, "Personalizar"), 
  c = Table.Group(
    b, 
    {"Numbers", "Personalizar"}, 
    {{"Contagem", each Table.RowCount(_), Int64.Type}}
  ), 
  d = Table.SelectRows(c, each (Number.IsOdd([Contagem]) = true)), 
  e = Table.Group(
    d, 
    {"Numbers"}, 
    {{"Contagem", each Text.Combine(List.Transform([Personalizar], Text.From), ", ")}}
  ), 
  f = Fonte[[Numbers]], 
  g = Table.NestedJoin(f, {"Numbers"}, e, {"Numbers"}, "f", JoinKind.LeftOuter), 
  Result = Table.ExpandTableColumn(g, "f", {"Contagem"}, {"Contagem"})[[Contagem]]
in
  Result
Power Query solution 4 for Numbers with Odd Frequencies, proposed by Brian Julius: 
let
  Source = Table.DuplicateColumn(OddRaw, "Numbers", "Numbers2", Int64.Type), 
  Split = Table.TransformColumnTypes(
    Table.ExpandListColumn(
      Table.TransformColumns(
        Source, 
        {
          {
            "Numbers2", 
            Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
            let
              itemType = (type nullable text) meta [Serialized.Text = true]
            in
              type {itemType}
          }
        }
      ), 
      "Numbers2"
    ), 
    {"Numbers2", Int64.Type}
  ), 
  Group = Table.SelectRows(
    Table.Group(Split, {"Numbers", "Numbers2"}, {{"Count", each Table.RowCount(_), Int64.Type}}), 
    each Number.IsOdd([Count])
  ), 
  Concat = Table.Group(
    Group, 
    {"Numbers"}, 
    {"AnswerExpected", each List.Sort([Numbers2], Order.Ascending)}
  ), 
  Extract = Table.RenameColumns(
    Table.TransformColumns(
      Concat, 
      {"AnswerExpected", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
    ), 
    {"Numbers", "NumberJoin"}
  ), 
  Join = Table.SelectColumns(
    Table.Join(Source, "Numbers", Extract, "NumberJoin", JoinKind.LeftOuter), 
    {"Numbers", "AnswerExpected"}
  )
in
  Join
Power Query solution 5 for Numbers with Odd Frequencies, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.AddColumn(
    Source, 
    "Answer Expected", 
    each Text.Combine(
      List.Transform(
        List.Sort(
          Table.SelectRows(
            Table.Group(
              Table.FromColumns(
                {List.Transform(Splitter.SplitTextByDelimiter(", ")([Numbers]), Number.From)}, 
                {"Numbers"}
              ), 
              {"Numbers"}, 
              {{"Count", each Table.RowCount(_)}}
            ), 
            each Number.IsOdd([Count])
          )[Numbers]
        ), 
        Text.From
      ), 
      ", "
    )
  )
in
  Result
Power Query solution 7 for Numbers with Odd Frequencies, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Numbers", type text}}),
 #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
 #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Numbers", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Numbers"),
 #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Numbers", Int64.Type}}),
 #"Grouped Rows" = Table.Group(#"Changed Type1", {"Numbers", "Index"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
 #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Index", "Numbers", "Count"}),
 #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if Number.Mod([Count],2)=1 then 1 else 0),
 
 #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
 Removed = Table.SelectColumns(#"Filtered Rows1",{"Index", "Numbers"}),
 Tbl = #"Added Index",
 Custom1 = Table.NestedJoin(Tbl,{"Index"},Removed,{"Index"},"Tbl",JoinKind.LeftOuter),
 #"Expanded Tbl" = Table.ExpandTableColumn(Custom1, "Tbl", {"Numbers"}, {"Numbers.1"}),
 #"Sorted Rows" = Table.Sort(#"Expanded Tbl",{{"Index", Order.Ascending}}),
 #"Changed Type2" = Table.TransformColumnTypes(#"Sorted Rows",{{"Numbers.1", type text}}),
 #"Grouped Rows1" = Table.Group(#"Changed Type2", {"Numbers"}, {{"Numbers.1", each Text.Combine([Numbers.1]," ,"), type nullable number}}),
 #"Changed Type3" = Table.TransformColumnTypes(#"Grouped Rows1",{{"Numbers.1", type text}}),
 #"Removed Other Columns" = Table.SelectColumns(#"Changed Type3",{"Numbers.1"})
in
 #"Removed Other Columns"
Power Query solution 8 for Numbers with Odd Frequencies, proposed by Matthias Friedmann: 
let
 Source = Excel.CurrentWorkbook(){[Name = "OddNumberOfTimes"]}[Content],
 Transform = Table.TransformColumns(
 Source,
 {
 "Numbers",
 each [
 a = Text.Split(_, ", "),
 b = List.Distinct(a),
 c = List.Select(
 b, 
 (z) => not Number.IsEven(List.Count(List.Select(a, each _ = z)))
 ),
 d = Text.Combine(List.Sort(c, each Number.From(_)), ", ")
 ][d]
 }
 )
in
 Transform


Alternative annotation for the List.Select step to show that z is just a variable definition referring  to the elements of b, i.e. List.Distinct(a):
 c = List.Select(b, each 
 let
 z = _
 in
 not Number.IsEven(List.Count(List.Select(a, each _ = z)))
 ),
Power Query solution 9 for Numbers with Odd Frequencies, proposed by Matthias Friedmann: 
let
  Source = Excel.CurrentWorkbook(){[Name = "OddNumberOfTimes"]}[Content], 
  Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), 
  Split = Table.ExpandListColumn(
    Table.TransformColumns(
      Index, 
      {
        {
          "Numbers", 
          Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "Numbers"
  ), 
  Trimmed = Table.TransformColumns(Split, {{"Numbers", Text.Trim, type text}}), 
  Grouped = Table.Group(
    Trimmed, 
    {"Numbers", "Index"}, 
    {{"Count", each Table.RowCount(_), Int64.Type}}
  ), 
  Filtered = Table.SelectRows(Grouped, each not Number.IsEven([Count])), 
  Grouped1 = Table.Group(
    Filtered, 
    {"Index"}, 
    {{"Numbers", each Text.Combine(List.Sort([Numbers]), ", ")}}
  ), 
  Merged = Table.NestedJoin(
    Index[[Index]], 
    {"Index"}, 
    Grouped1, 
    {"Index"}, 
    "Grouped Rows1", 
    JoinKind.LeftOuter
  ), 
  Expanded = Table.ExpandTableColumn(Merged, "Grouped Rows1", {"Numbers"}, {"Numbers"})[[Numbers]]
in
  Expanded
Power Query solution 10 for Numbers with Odd Frequencies, proposed by Victor Wang: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = List.Transform(
    Source[Numbers], 
    each 
      let
        s = Text.Split(_, ", ")
      in
        Text.Combine(
          List.Distinct(
            List.Select(s, (a) => Number.IsOdd(List.Count(List.Select(s, (b) => a = b))))
          ), 
          ", "
        )
  )
in
  Result
Power Query solution 11 for Numbers with Odd Frequencies, proposed by Mahmoud Bani Asadi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), 
  SplitCol = Table.ExpandListColumn(
    Table.TransformColumns(
      Index, 
      {
        {
          "Numbers", 
          Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "Numbers"
  ), 
  Group = Table.Group(
    SplitCol, 
    {"Numbers", "Index"}, 
    {{"Count", each Table.RowCount(_), Int64.Type}}
  ), 
  CheckOdd = Table.TransformColumns(Group, {{"Count", Number.IsOdd, type logical}}), 
  FilterOdd = Table.SelectRows(CheckOdd, each ([Count] = true)), 
  Group1 = Table.Group(
    FilterOdd, 
    {"Index"}, 
    {{"Count", each Text.Combine(List.Sort([Numbers]), ", "), type nullable text}}
  ), 
  MergeQ = Table.NestedJoin(
    Group1, 
    {"Index"}, 
    Index, 
    {"Index"}, 
    "Grouped Rows1", 
    JoinKind.RightOuter
  ), 
  Expand = Table.ExpandTableColumn(
    MergeQ, 
    "Grouped Rows1", 
    {"Numbers", "Index"}, 
    {"Numbers", "Index.1"}
  ), 
  Sort = Table.Sort(Expand, {{"Index.1", Order.Ascending}}), 
  RemoveOther = Table.SelectColumns(Sort, {"Numbers", "Count"})
in
  RemoveOther
Power Query solution 12 for Numbers with Odd Frequencies, proposed by Mahmoud Bani Asadi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.AddColumn(
    Source, 
    "Odd Count", 
    each [
      a = Text.Split([Numbers], ", "), 
      b = List.Select(a, each Number.IsOdd(List.Count(List.PositionOf(a, _, 2)))), 
      c = Text.Combine(List.Sort(List.Distinct(b)), ", ")
    ][c]
  )
in
  Result
Power Query solution 13 for Numbers with Odd Frequencies, proposed by Jan Willem Van Holst: 
let
 Source = Your Data
 #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Numbers", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}}), "Numbers"),
 #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Index", "Numbers"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
 #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Number.IsOdd([Count])),
 #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
 #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count", "Custom"}),
 #"Grouped Rows1" = Table.Group(#"Removed Columns", {"Index"}, {{"Data", each Text.Combine([Numbers],", "), type table [Index=number, Numbers=nullable text]}}),
 #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Grouped Rows1", {"Index"}, "Grouped Rows1", JoinKind.LeftOuter),
 #"Expanded Grouped Rows1" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows1", {"Data"}, {"Data"}),
 #"Removed Columns1" = Table.RemoveColumns(#"Expanded Grouped Rows1",{"Index"})
in
 #"Removed Columns1"
Power Query solution 14 for Numbers with Odd Frequencies, proposed by Thomas DUCROQUETZ: 
let
  Source = YourRawData, 
  Answer = Table.AddColumn(
    Source, 
    "Answer Expected", 
    each 
      let
        NumbersInList = Text.Split([Numbers], ", "), 
        distinctNumbers = List.Distinct(NumbersInList), 
        OddNumberOfTimes = List.Select(
          distinctNumbers, 
          each 
            let
              currentNumber = _, 
              CountNumbers  = List.Count(List.Select(NumbersInList, each _ = currentNumber))
            in
              Number.IsOdd(CountNumbers)
        )
      in
        Text.Combine(List.Sort(OddNumberOfTimes, Order.Ascending), ", "), 
    type text
  )
in
  Answer

Solving the challenge of Numbers with Odd Frequencies with Excel

Excel solution 1 for Numbers with Odd Frequencies, proposed by Bo Rydobon 🇹🇭: 
=MAP(
    A2:A7,
    LAMBDA(
        a,
        LET(
            s,
            SORT(
                --TEXTSPLIT(
                    a,
                    ,
                    ","
                )
            ),
            TEXTJOIN(
                ", ",
                ,
                IF(
                    MOD(
                        FREQUENCY(
                            s,
                            s
                        ),
                        2
                    ),
                    s,
                    ""
                )
            )
        )
    )
)


=MAP(
    A2:A7,
    LAMBDA(
        a,
        LET(
            s,
            SORT(
                --TEXTSPLIT(
                    a,
                    ,
                    ","
                )
            ),
            ARRAYTOTEXT(
                FILTER(
                    s,
                    MOD(
                        DROP(
                            FREQUENCY(
                            s,
                            s
                        ),
                            -1
                        ),
                        2
                    ),
                    ""
                )
            )
        )
    )
)
Excel solution 2 for Numbers with Odd Frequencies, proposed by Rick Rothstein: 
=MAP(
    A2:A8,
    LAMBDA(
        z,
        TEXTJOIN(
            ", ",
            ,
            SORT(
                MAP(
                    UNIQUE(
                        0+TEXTSPLIT(
                            z,
                            ", "
                        ),
                        1
                    ),
                    LAMBDA(
                        x,
                        IF(
                            ISODD(
                                COUNTA(
                                    TEXTSPLIT(
                                        " "&z&",",
                                        " "&x&","
                                    )
                                )-1
                            ),
                            x,
                            ""
                        )
                    )
                ),
                ,
                ,
                1
            )
        )
    )
)
Excel solution 3 for Numbers with Odd Frequencies, proposed by John V.:

_x000D_

_x000D_

Excel solution 4 for Numbers with Odd Frequencies, proposed by محمد حلمي: 

=MAP(A2:A7,LAMBDA(a,
LET(
c,TEXTSPLIT(a,,",")+0,
TEXTJOIN(",",,SORT(UNIQUE(FILTER(c,ISODD(
MAP(c,LAMBDA(x,SUM(--(x=c))))),"")))))))

 

Excel solution 5 for Numbers with Odd Frequencies, proposed by Julian Poeltl: 

=IFERROR(MAP(A2:A7,LAMBDA(N,LET(SP,TEXTSPLIT(N,", "),U,UNIQUE(SP,1),TEXTJOIN(", ",,SORT(--FILTER(U,ISODD(MAP(U,LAMBDA(A,COUNTA(FILTER(SP,SP=A)))))),,,1))))),"")

 

Excel solution 6 for Numbers with Odd Frequencies, proposed by Aditya Kumar Darak 🇮🇳: 

=MAP(
 A2:A7,
    
 LAMBDA(a,
    
 LET(
 s,
     TEXTSPLIT(
         a,
          ,
          ", "
     ),
    
 u,
     UNIQUE(
         s
     ),
    
 e,
     LAMBDA(x,
     (XOR(
         s = x
     ))),
    
 c,
     MAP(
         u,
          e
     ),
    
 r,
     ARRAYTOTEXT(
         SORT(
             FILTER(
                 u,
                  c,
                  ""
             )
         )
     ),
    
 r
 )
 )
)

 

Excel solution 7 for Numbers with Odd Frequencies, proposed by Timothée BLIOT: 

=LET(Data, IFERROR(TEXTSPLIT(TEXTJOIN("/",1,A2:A7),", ","/",1),""),
OccuRow, MOD(MAKEARRAY(ROWS(Data),COLUMNS(Data),LAMBDA(a,b,
SUMPRODUCT(1*(INDEX(Data,a,b)=INDEX(Data,a,))))),2),
Answer, MAKEARRAY(ROWS(Data),COLUMNS(Data),LAMBDA(a,b,
IF(INDEX(OccuRow,a,b)=1,INDEX(Data,a,b),"") )),
BYROW(Answer, LAMBDA(a, TEXTJOIN(", ",1,UNIQUE(TRANSPOSE(a))))))

 

Excel solution 8 for Numbers with Odd Frequencies, proposed by Duy Tùng: 

=MAP(A2:A7,LAMBDA(v,LET(a,TEXTSPLIT(v,,", "),b,GROUPBY(a,a,ROWS,,0),ARRAYTOTEXT(FILTER(TAKE(b,,1),ISODD(DROP(b,,1)),"")))))

 

Excel solution 9 for Numbers with Odd Frequencies, proposed by Bhavya Gupta: 

=MAP(A2:A7,LAMBDA(N,LET(S,TEXTSPLIT(N,,",")+0,U,UNIQUE(S),ARRAYTOTEXT(SORT(FILTER(U,ISODD(DROP(FREQUENCY(S,U),-1)),""))))))

 

Excel solution 10 for Numbers with Odd Frequencies, proposed by Charles Roldan: 

=LET(_Tally, LAMBDA(y, BYROW(y=TOROW(y), LAMBDA(z, SUM(--z)))), MAP(A2:A7, LAMBDA(x, LET(w, TEXTSPLIT(x, , ", "), IFERROR(ARRAYTOTEXT(SORT(UNIQUE(FILTER(w, MOD(_Tally(w), 2))))), "")))))

 

Excel solution 11 for Numbers with Odd Frequencies, proposed by Stefan Olsson: 

=BYROW(A2:A7,
 LAMBDA(rr,
 IFERROR(TEXTJOIN(", ",true,QUERY(QUERY({TRANSPOSE(SPLIT(rr,","))},"Select Col1, Count(Col1) Group by Col1 Label Count(Col1) ''",0),"Select Col1 Where Col2 Matches '"&TEXTJOIN("|",true,SEQUENCE(1,100,1,2))&"'",0)))))

 

Excel solution 12 for Numbers with Odd Frequencies, proposed by Victor Momoh (MVP, MOS, R.Eng): 

=BYROW(
    A2:A7,
    
    LAMBDA(
        x,
        
        LET(
            p,
            SORT(
                --TEXTSPLIT(
                    x,
                    ,
                    ", "
                )
            ),
            
            q,
            UNIQUE(
                p
            ),
            
            TEXTJOIN(
                ", ",
                ,
                
                FILTER(
                    q,
                    ISODD(
                        DROP(
                            FREQUENCY(
                                p,
                                q
                            ),
                            -1
                        )
                    ),
                    ""
                )
            )
        )
    )
)

 

Excel solution 13 for Numbers with Odd Frequencies, proposed by Victor Momoh (MVP, MOS, R.Eng): 

=BYROW(A2:A7,
    
LAMBDA(x,
    
REDUCE("",
    SORT(
        --UNIQUE(
            TEXTSPLIT(
                x,
                ,
                ", "
            )
        )
    ),
    
LAMBDA(a,
    b,
    TEXTJOIN(", ",
    1,
    a,
    IF(ISODD((LEN(
        x
    )-LEN(
        SUBSTITUTE(
            x,
            b,
            ""
        )
    ))/LEN(
        b
    )),
    b,
    ""))))))

 

Excel solution 14 for Numbers with Odd Frequencies, proposed by Abhishek Kumar Jain: 

=LET(a,--TEXTSPLIT($A2,,", "),b,FREQUENCY(a,a),c,b&"_"&SEQUENCE(COUNT(b)),d,IF(ISODD(--TEXTBEFORE(c,"_")),INDEX(a,--TEXTAFTER(c,"_"))),IFERROR(TEXTJOIN(", ",TRUE,SORT(FILTER(d,d<>FALSE))),""))

 

Excel solution 15 for Numbers with Odd Frequencies, proposed by Mahmoud Bani Asadi: 

=BYROW(Table1[Numbers],
LAMBDA(data,
LET(
a,TEXTSPLIT(data,,", "),
b,MAP(a,LAMBDA(x,COUNTA(FILTER(a,a=x)))),
c,UNIQUE(FILTER(a,ISODD(b),"")),
d,SORT(c),
e,ARRAYTOTEXT(d),e)))

 

Excel solution 16 for Numbers with Odd Frequencies, proposed by Vijay Tumbur: 

=BYROW(A2:A7,LAMBDA(a,TEXTJOIN(", ",TRUE,SORT(FILTER(UNIQUE(TEXTSPLIT(a,,",")*1),MOD(DROP(FREQUENCY(TEXTSPLIT(a,,",")*1,UNIQUE(TEXTSPLIT(a,,",")*1)),-1),2)=1,"")))))


Solving the challenge of Numbers with Odd Frequencies with Python



Python solution 1 for Numbers with Odd Frequencies, proposed by Igor Perković: 

import pandas as pd
# Reading source data
df = pd.read_excel('ONoT.xlsx', sheet_name = 'Data')
acc = {}
data = df['Numbers'].values.tolist()
for l in data:
 lst = l.split(',')
 freq = {}
 for i in lst:
 if (i.strip() in freq):
 freq[i.strip()] += 1
 else:
 freq[i.strip()] = 1
 res = []
 for k,v in freq.items():
 if v%2 != 0:
 res.append(k)
 acc[l] = ','.join(sorted(res))
# Result preparation
dt = pd.DataFrame.from_dict(acc, orient='index', columns=['Answer'])
dt['Number'] = dt.index
res = dt.loc[:,['Number', 'Answer']]
res.to_excel('Result_89.xlsx', index=False)


Solving the challenge of Numbers with Odd Frequencies with SQL

 

SQL solution 1 for Numbers with Odd Frequencies, proposed by Zoran Milokanović: 

WITH
DATA_PREPARATION
AS
(
 SELECT
 ROW_NUMBER() OVER () AS ORDINAL_NUMBER
 ,LENGTH(REGEXP_REPLACE(D.NUMBERS, '[^,]+', '')) /*NO_OF_DELIMITERS*/ + 1 AS NO_OF_NUMBERS
 ,D.NUMBERS
 FROM DATA D
),
NUMBER_LIMIT
AS
(
 SELECT
 ROW_NUMBER() OVER () AS ROW
 FROM DATA_PREPARATION X CROSS JOIN DATA_PREPARATION Y
)
SELECT
 F.NUMBERS
FROM
(
 SELECT
 DP.ORDINAL_NUMBER
 ,DP.NUMBERS
 ,TRIM(SPLIT_PART(DP.NUMBERS, ',', NL.ROW)) AS NUMBER
 ,COUNT(*) AS OCCURANCE
 FROM DATA_PREPARATION DP
 CROSS JOIN NUMBER_LIMIT NL
 WHERE
 NL.ROW <= DP.NO_OF_NUMBERS
 GROUP BY
 DP.ORDINAL_NUMBER
 ,DP.NUMBERS
 ,TRIM(SPLIT_PART(DP.NUMBERS, ',', NL.ROW))
) F
GROUP BY
 F.ORDINAL_NUMBER
,F.NUMBERS
ORDER BY
 F.ORDINAL_NUMBER
;

 

Leave a Reply