0

I have a table that looks like this

CREATE TABLE foo (id, name, category)
AS VALUES
  ( 1, 'name1.1', 'cat1.1.1'),
  ( 1, 'name1.2', 'cat1.1.1'),
  ( 1, 'name1.3', 'cat1.2.1'),
  ( 2, 'name2.1', 'cat2.1.1'),
  ( 2, 'name2.2', 'cat2.1.1'),
  ( 3, 'name3.1', 'cat3.1.1')
;

I'm trying to get a result that looks like this,

Id name1 name2 name3
1 name1.1 name1.2 name1.3
2 name2.1 name2.2
3 name3.1
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
John Doe
  • 13
  • 2

2 Answers2

0

First you'll need to add the tablefunc module which provides this ability.

CREATE EXTENSION tablefunc;

Now you need a query like this,

SELECT *
FROM crosstab(
    -- here we normalize this into `id | cat | value`
    -- where the category is [1-3]
    $$SELECT id, RIGHT(name,1)::int AS cat, name AS value FROM foo ORDER BY 1,2;$$,
    -- For just the cat 1,2,3
    $$VALUES (1),(2),(3);$$
) AS ct(id text, name1 text, name2 text, name3 text);

Which will return this,

 id |  name1  |  name2  |  name3  
----+---------+---------+---------
 1  | name1.1 | name1.2 | name1.3
 2  | name2.1 | name2.2 | 
 3  | name3.1 |         | 
(3 rows)

Notice the big gotcha here, your catagory is actually a function of your data RIGHT(name,1)::int. That was probably your hold up. Conversely, the actual data you provided in category is seemingly safe to ignore entirely unless I'm missing something.

Also note that I hardcoded the catagory names in two places,

$$VALUES (1),(2),(3);$$

And,

ct(id text, name1 text, name2 text, name3 text);

This is required, as PostgreSQL doesn't allow you to return a query where the result set is not known when you run the command. This will support only [name1 - name3]If you want it truly dynamic the scope of the program grows a lot and it's off topic on this question.

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
  • I think I should have been clearer in my question. I cannot extract the id from the name. It's always followed by random numbers. Eg: John_054745460131 will not be the same as Jane_054745460131. You were right to ignore the 3rd category column. So, I only have the 2 columns(id and name) to rely on. – John Doe Jan 17 '22 at 23:07
  • @JohnDoe Nothing changes =) Just update the values. I was just showing you the ["Minimal, Reproducible Example"](https://stackoverflow.com/help/minimal-reproducible-example). Try this answer with this data, `CREATE TABLE foo (id, name, category) AS VALUES ( 'john_1', 'name1.1', 'cat1.1.1'), ( 'john_1', 'name1.2', 'cat1.1.1'), ( 'jane_1', 'name1.3', 'cat1.2.1'), ( 'john_2', 'name2.1', 'cat2.1.1'), ( 'jane_2', 'name2.2', 'cat2.1.1'), ( 'john_3', 'name3.1', 'cat3.1.1') ;` I assume that's still what you want. – Evan Carroll Jan 18 '22 at 03:05
  • This is the data I'm working with. CREATE TABLE foo (id, name, category) AS VALUES ( '1', 'john1.1', 'cat1.1.1'), ( '1', 'john1.2', 'cat1.1.1'), ( '1', 'john1.3', 'cat1.2.1'), ( '2', 'jane2.1', 'cat2.1.1'), ( '2', 'jane2.2', 'cat2.1.1'), ( '3', 'jake3.1', 'cat3.1.1') – John Doe Jan 18 '22 at 04:12
  • @JohnDoe At this point, I simply do not understand what you want. I would very cautiously edit this question though and update it with the right information but change nothing of the form! Follow the current form of question. Also update and show your desired output. – Evan Carroll Jan 18 '22 at 04:34
0

I would aggregate all names into an array, then extract the array elements as columns:

select id, 
       names[1] as name1,
       names[2] as name2,
       names[3] as name3
from (
   select id, 
          array_agg(name order by name) as names
   from foo
   group by id
) t

If the names could contain things like name10.11 then the order of the columns won't be numerical because the string '10' is lower than the string '2'. If you want the order to reflect the numbers, the sorting gets a bit more complicated:

array_agg(name order by string_to_array(replace(name, 'name', ''), '.')::int[]) as names

This removes the name prefix and converts the numbers to an integer array which then sorts properly. Another option is to remove everything that's not a digit or dot:

array_agg(name order by string_to_array(regexp_replace(name, '[^0-9.]', '', 'g'), '.')::int[]) as names