CODEWITHSUNDEEP
sqlpostgresql-9.1
Durga Gaddam
Durga Gaddam

Reputation: 355

How to get column distinct values of multiple columns as array with corresponding column name?

I am trying to get the distinct values of multiple columns as arrays. I have tried use the function array_agg

    Color  |  State     |  Item
=================================
    Red    |  New York  |  Balloon
    Pink   |  Virginia  |  Table
    Black  |  Utah      |  Table

I want a table as

    Column_name  | Options
===============================
    Color        | [Red,Pink,Black]
    State        | [New York,Virginia,Utah]
    Item         | [Balloon,Table]

I have tried this:

select (array_agg(distinct "Color"))  from sample_table
union 
select (array_agg(distinct "State")) from sample_table
union 
select (array_agg(distinct "Item")) from sample_table
union

I am stuck at how to get the corresponding column names..!

Upvotes: 1

Views: 758

Answers (1)

Sookie Singh
Sookie Singh

Reputation: 1623

You just have to add your column_names like this:

SELECT 'Color' AS column_name, array_agg(distinct "Color") AS options from sample_table
UNION
SELECT 'State' AS column_name, array_agg(distinct "State") AS options from sample_table
UNION
SELECT 'Item' AS column_name, array_agg(distinct "Item") AS options from sample_table

Working Fiddle

Upvotes: 1

Related Questions