CODEWITHSUNDEEP
sqlpostgresqlpivot
Erik Lundin
Erik Lundin

Reputation: 47

SQL query with new column for each unique attribute in table

A bit of background: I have a table in my Postgres database

daily(date DATE, ticker VARCHAR(10), adjusted_close FLOAT), e.g.

date ticker adjusted_close
2021-02-01 ABC 23.2
2021-02-01 CCC 222.2
2021-02-02 ABC 24.2
2021-02-02 CCC 220.4

And I want to make a query that, no matter how many unique tickers exist, returns a table like this one:

date ABC CCC
2021-02-01 23.2 222.2
2021-02-02 24.2 220.4

I have read about the PIVOT() function in Postgres, but I cannot figure out a way to get it to work. Has anyone encountered the same issue and figured out a solution?

Upvotes: 0

Views: 249

Answers (2)

Kristian Fitzgerald
Kristian Fitzgerald

Reputation: 269

This is working for me in mssql. Hopefully it works in Postgres too.

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.ticker) 
FROM daily c
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
,1,1,'')


set @query = 'SELECT [date], ' + @cols + ' from 
        (
           SELECT  [date]
           ,[ticker]
           ,[adjusted_close]
           FROM daily
        ) x
        pivot 
        (
            min([adjusted_close])
            for [ticker] in (' + @cols + ')
        ) p '

execute(@query)

Upvotes: 1

user330315
user330315

Reputation:

In Postgres PIVOT is typically done using filtered aggregation:

select d.date, 
       max(d.adjusted_close) filter (where d.ticker = 'ABC') as abc,
       max(d.adjusted_close) filter (where d.ticker = 'CCC') as ccc
from daily d
group by d.date;

And before you ask: you can not make this "dynamic". The number, names and data type of a query must be known when the query is parsed and before it is actually executed. You can't have a query where the columns are determined only after the query started.

An alternative might be to aggregate everything for a single date into a JSON value:

select d.date, 
       jsonb_object_agg(d.ticker, d.adjusted_close) as ticker_info
from daily d
group by d.date;

Upvotes: 1

Related Questions