CODEWITHSUNDEEP
snowflake-cloud-data-platform
Shanoo
Shanoo

Reputation: 1255

How to retain column order using listagg in snowflake

I have a Snowflake table A as below:

CREATE or replace table TABLEA
(
    Address VARCHAR(50),
    Zip VARCHAR(50),
    HASH VARCHAR(50));

insert into TABLEA (Address, Zip, HASH) values
    ('ABC', '20987', 'XX'),
    ('XYZ', '20989', 'XY'),
    ('CBZ', '20980', 'XZ');

I want to get all the column names from information schema as a string seperated by comma exactly like the below query outputs

select listagg(COLUMN_NAME, ',') from db.information_schema.columns 
              where table_schema = 'schema' and TABLE_NAME = 'TABLEA'

but the problem with the above query is it doesnot retain the column order meaning when I execute the query 1st time, it outputs

ZIP,HASH,ADDRESS

whereas if I execute the same query again, it outputs

ADDRESS,ZIP,HASH

The column order is not the same, I want the column order to be consistent (as in the table) every time the query is executed

UPDATE

Adding order by ORDINAL_POSITION worked

select listagg(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY ORDINAL_POSITION) from db.information_schema.columns 
                  where table_schema = 'schema' and TABLE_NAME = 'TABLEA'

Upvotes: 1

Views: 1166

Answers (1)

NickW
NickW

Reputation: 9778

you just need to ORDER BY ORDINAL_POSITION

Upvotes: 2

Related Questions