How can I transpose table values in postgresql?

Question

How can I transpose these values to take this table:

And make it look like this:

There's no Pivot function that I can use in postgresql and every time I try the crosstab function, I keep getting an error in my code. Let's pretend that my table is called select * from TAttributes

Does this answer your question? Pivot Row on PostgreSQL

eshirvana
– eshirvana

2021-04-29 18:09:26 +00:00
Commented Apr 29, 2021 at 18:09 — eshirvana
– eshirvana, Commented Apr 29, 2021 at 18:09

Gordon Linoff · Accepted Answer · 2021-04-30 00:52:08Z

1

Use conditional aggregation, which in Postgres means filter:

select id,
       max(attributenumber) filter (where attributeid = 2000),
       max(attributenumber) filter (where attributeid = 2001),
       max(attributenumber) filter (where attributeid = 2002),
       max(attributenumber) filter (where attributeid = 2003)
from t
group by id;

Here is a db<>fiddle.

edited Apr 30, 2021 at 0:52

answered Apr 29, 2021 at 18:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

oaker Over a year ago

unfortunately, this just gives me null values throughout

Gordon Linoff Over a year ago

@oaker . . . I added a db<>fiddle. The code works and answers your question.

oaker Over a year ago

Looks like it worked! Can I do the same with dates? for example, max(attributedate) filter (where attributeid = 2007), etc. I'm trying that out but it's giving me an ERROR: sytanx error at or near "max"

Gordon Linoff Over a year ago

Yes, you can use dates. MAX() works on pretty much all data types.

SGiux · Accepted Answer · 2021-04-29 21:30:52Z

0

Try this:

SELECT id,  
    MAX((SELECT attributenumber FROM public.t WHERE ext_t.id = id AND ext_t.attribuiteid = attribuiteid AND attribuiteid = 2000)) AS '2000',
    MAX((SELECT attributenumber FROM public.t WHERE ext_t.id = id AND ext_t.attribuiteid = attribuiteid AND attribuiteid = 2001)) AS '2001',
    MAX((SELECT attributenumber FROM public.t WHERE ext_t.id = id AND ext_t.attribuiteid = attribuiteid AND attribuiteid = 2002)) AS '2002',
    MAX((SELECT attributenumber FROM public.t WHERE ext_t.id = id AND ext_t.attribuiteid = attribuiteid AND attribuiteid = 2003)) AS '2003' 
FROM public.t ext_t GROUP BY id

answered Apr 29, 2021 at 21:30

SGiux

8833 gold badges16 silver badges41 bronze badges

Collectives™ on Stack Overflow

How can I transpose table values in postgresql?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related