Hi Being new to POSTGRESQL, I need to convert the multiple rows of data to one row and several columns. How to do it in POSTGRESQL. I want to convert the below data
1
-
Did you attach an image? If so it is not displaying.cazort– cazort2021-08-25 14:41:07 +00:00Commented Aug 25, 2021 at 14:41
Add a comment
|
2 Answers
You can use the bool_or() aggregate function to achieve this:
select movie,
bool_or(genre = 'Action') as is_action,
bool_or(genre = 'Fantasy') as is_fantasy,
bool_or(genre = 'Crime') as is_crime,
bool_or(genre = 'Comedy') as is_comedy
from the_table
group by movie
Note that this returns a real boolean value rather than 0 or 1 which I find much more logical for such a "flag" column. If you do prefer integers over boolean for a true/false flag, just cast the function's result to int, e.g. bool_or(....)::int
1 Comment
bfris
nice. I wasn't aware of that function.
Here's a brute force way to do it. It could get tedious if you have a lot of genres.
First create tables and insert data. I created another movie for illustrative purposes:
CREATE TABLE t1 (movie text, genre text);
INSERT INTO t1 (movie, genre)
VALUES ('ABC', 'Action'),
('ABC', 'Fantasy'),
('DEF', 'Comedy'),
('DEF', 'Action');
To get the columns you want, first you can just create one row per movie per genre:
SELECT movie,
CASE WHEN genre = 'Action' THEN 1 ELSE 0 END AS isAction,
CASE WHEN genre = 'Fantasy' THEN 1 ELSE 0 END AS isFantasy,
CASE WHEN genre = 'Comedy' THEN 1 ELSE 0 END AS isComedy,
CASE WHEN genre = 'Crime' THEN 1 ELSE 0 END AS isCrime
FROM t1
which gives too many rows
| movie | isaction | isfantasy | iscomedy | iscrime |
|---|---|---|---|---|
| ABC | 1 | 0 | 0 | 0 |
| ABC | 0 | 1 | 0 | 0 |
| DEF | 0 | 0 | 1 | 0 |
| DEF | 1 | 0 | 0 | 0 |
But we can reduce the number of rows by doing a sum and GROUP BY genre
WITH t2 as (
SELECT movie,
CASE WHEN genre = 'Action' THEN 1 ELSE 0 END AS isAction,
CASE WHEN genre = 'Fantasy' THEN 1 ELSE 0 END AS isFantasy,
CASE WHEN genre = 'Comedy' THEN 1 ELSE 0 END AS isComedy,
CASE WHEN genre = 'Crime' THEN 1 ELSE 0 END AS isCrime
FROM t1)
SELECT movie, sum(isAction) as isAction,
sum(isFantasy) as isFantasy,
sum(isComedy) as isComedy,
sum(isCrime) as isCrime
FROM t2
GROUP BY movie
which gives
| movie | isaction | isfantasy | iscomedy | iscrime |
|---|---|---|---|---|
| ABC | 1 | 1 | 0 | 0 |
| DEF | 1 | 0 | 1 | 0 |
Now for some discussion on data types and getting this data into a table. As written these queries will have isAction, etc. columns as bigint. You might actually want boolean, in which case you have to cast first to int and then to boolean (e.g. sum(isAction)::int::bool as isAction...).
To get the data into a table replace the last SELECT query with a SELECT INTO query which will automatically create a new table for you.