# Show all the columns in a projection's ORDER by clause

select distinct case when p.is_Segmented then 'Segmented' else 'Replicated' end, p.projection_name, p.segment_expression, pc.num_cols, pc.order_by

from projections p

join (select projection_id

, count(1) over (partition by projection_id ) as num_cols

, nth_value(projection_column_name, 1) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following) || ', ' ||

coalesce(nth_value(projection_column_name, 2) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 3) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 4) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 5) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 6) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 7) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 8) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 9) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 10) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 11) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 12) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 13) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 14) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 15) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 16) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 17) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 18) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 19) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 20) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 21) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 22) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 23) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 24) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 25) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 26) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 27) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 28) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 29) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||

coalesce(nth_value(projection_column_name, 30) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') as order_by

from projection_columns where sort_position is not null ) pc using (projection_id)

where lower(projection_schema) = 'public' and lower(anchor_Table_name) = 'vendor_dimension'

order by 2;

output:

case | projection_name | segment_expression | num_cols | order_by

-----------+---------------------+-----------------------------------+----------+--------------------------------------------------------------------------------------------------------------

Segmented | vendor_dimension_b0 | hash(vendor_dimension.vendor_key) | 1 | vendor_key, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Segmented | vendor_dimension_b1 | hash(vendor_dimension.vendor_key) | 1 | vendor_key, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

(2 rows)

## Comments

You can also make use of the new LISTAGG function to show a projection's sort order!

Example: