Postgres pivot results with no unique identifier to group by

Question

I'm having a little trouble pivoting this query... Usually there is an identifier or something common to group the data, but in this particular case there isn't, any help is appreciated!

lets say i have a sample table like so

CREATE TABLE test (
    id text primary key,
    created_at date not null default CURRENT_DATE
);

INSERT INTO test (id) VALUES
('AS00334455'),
('AS009988'),
('AS0011223'),
('AS00908800'),
('PS00555555'),
('PS00333333'),
('PS00444444');

visually it would look like this

|         id | created_at |
|------------|------------|
| AS00334455 | 2018-07-05 |
|   AS009988 | 2018-07-05 |
|  AS0011223 | 2018-07-05 |
| AS00908800 | 2018-07-05 |
| PS00555555 | 2018-07-05 |
| PS00333333 | 2018-07-05 |
| PS00444444 | 2018-07-05 |

I'd like to select results from this table where i get the ID column pivoted by the first 2 characters of the ID. visually that would look like this

|         AS |         PS |
|------------|------------|
| AS00334455 | PS00555555 |
|   AS009988 | PS00333333 |
|  AS0011223 | PS00444444 |
| AS00908800 |            |

what i tried so far was this, but im stuck on what group by to apply to render the results appropriately

SELECT
  MAX(case when t.id_type = 'AS' then t.id else '' end) as AS,
  MAX(case when t.id_type = 'PS' then t.id else '' end) as PS
FROM
(  SELECT LEFT(id, 2) as id_type, id
   FROM test
) as t;

I could probably use a row count and nest inside another select, but im concerned about performance as the real table is already a few million records and there are a lot of other things happening in this query. So optimization is key

Fiddle to play around with

I'm far from an expert on `crosstab`, but I think you can use it here. I have no idea how the performance is. Here is a really good crosstab example [PostgresSQL Crosstab](https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905) — BShaps, Jul 06 '18 at 00:46
@BShaps thanks, I had already considered crosstab but the issue with it is you can only pivot one piece of data, you have to do a whole other crosstab query for the next column you want to pivot which doesn't seem ideal — John Ruddell, Jul 06 '18 at 00:48
`('AS', 'PS')` are the only types? or you need a dynamic filter? — Juan Carlos Oropeza, Jul 06 '18 at 01:28
Can you add a column to include that type? I mean that is the best way to optimize the pivot. Otherwise I will have to create the column on the fly and wont be as fast. — Juan Carlos Oropeza, Jul 06 '18 at 01:30
Already planning to do that, I'm inheriting an existing database but optimizing a lot of slow queries and making schema changes to supplement that. — John Ruddell, Jul 06 '18 at 01:31

score 1 · Accepted Answer · answered Jul 06 '18 at 01:58

1

This is one option SQL DEMO:

WITH cte as (
  SELECT ROW_NUMBER() OVER (PARTITION BY LEFT(id, 2) ORDER BY id) as rn,
         LEFT(id, 2) as id_type, id             
  FROM test  
)
SELECT MAX( CASE WHEN id_type = 'AS' THEN id END) as "AS",
       MAX( CASE WHEN id_type = 'PS' THEN id END) as "PS"
FROM cte
GROUP BY rn
ORDER BY rn
;

OUTPUT

|         AS |         PS |
|------------|------------|
|  AS0011223 | PS00333333 |
| AS00334455 | PS00444444 |
| AS00908800 | PS00555555 |
|   AS009988 |     (null) |

answered Jul 06 '18 at 01:58

Juan Carlos Oropeza

47,252
12
78
118

Thanks for your answer, I think this is something i can extend to the rest of my query (I need to pivot like 5 columns). Let me try and get this hooked into the server query and see what i can do. – John Ruddell Jul 06 '18 at 03:30

Postgres pivot results with no unique identifier to group by

1 Answers1