16 lines
779 B
Text
16 lines
779 B
Text
# The following SQL statement will convert a table A representing an m by n matrix
|
|
# in (i,j,value) format to a table with m rows and (n+1) columns, the first being the row id:
|
|
pivot A on j using max(value) order by i;
|
|
|
|
# To exclude the row id column:
|
|
select columns(* exclude (i)) from (pivot A on j using max(value)) order by i;
|
|
|
|
# To ensure the columns are presented in their natural order,
|
|
# the above 'select' statement may need to be adjusted.
|
|
# If n < 10, the following would suffice:
|
|
select columns(* exclude (i)) from (pivot A on j using max(value)) order by i;
|
|
|
|
# For 10 to 99 columns:
|
|
select columns('^[1-9]$'), columns('^[1-9][0-9]') from (pivot A on j using max(value)) order by i;
|
|
|
|
# If there are more than 99 columns, the above can be extended in the obvious way.
|