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,

CodePudding user response：

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.

CodePudding user response：

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