【Question】

Table name: table

Field names: a  b  c  d  e  f

Detailed data: 1  2  1  1  2  3
+++++++++++++++++++++++++++++++++++++++++++++++++

Task: Count the number of “1”, “2” and “3” respectively.

【Answer】

The requirement is clear. To achieve it we’ll convert row data to column data and then count each unique value.

SQL needs a nested query to get each new column value and then UNION all values into a column. When there are a lot of fields in the source table, the query becomes very long.

SPL (Structured Process Language) handles this in a simple way no matter how many fields there are in the source table:

A1: Write the table data into a text file, say test.txt and read in the text data. The first line will be imported as field names.

A2: Convert values of the first record into an array, group it by unique values and count members in each group.

A1

A2