RaqForum 41 No.
4 View  • 
     ...

Row to column conversion involving uncertain columns

 

The query results of the Postgresql database return 6 columns, with the first column being the grouping column, the second column being the Value column within the group, and columns 3-6 being the Key columns within the group. Each record only has one Key column with a value, while the other Key columns are null. The position of the Key with a value for each record is uncertain.

Food

Value

Characteristics

Color

Location

Date

Fruit

10

total count


Fruit

3

apple


Fruit

1

orange


Fruit

4

banana


Fruit

2

cherry


Fruit

5

red

Fruit

4

yellow

Fruit

1

blue

Now we need to keep the grouping column unchanged, convert the details within the group from rows to columns, convert the values of non-null Key columns to new column names, and convert the values of Value column to new column values.

Food

total count

apple

orange

banana

cherry

red

yellow

blue

Fruit

10

3

1

4

2

5

4

1

SPL code:

　A

1

=postgresql.query("select * from tb where Food=?”,”Fruit”)

2

=A1.pivot(Food;[#3,#4,#5,#6].ifn(),Value)

A1: Query the database through JDBC.

A2: Use the pivot function to convert this group of records from row to column. The first parameter is the grouping column, the second parameter is the column name of the Key, and the ifn function takes the first non-null member in the set. The third parameter is the Value column.

Question source：https://stackoverflow.com/questions/78372861/using-sql-is-there-a-way-to-transform-a-dataset-that-makes-new-columns-for-data


 
 •  4 View  •