Say I have a dataset of survey responses like this:
| survey_id | user_id | question | answer |
|---|---|---|---|
| 1 | 1 | fav_colour | red |
| 1 | 2 | fav_colour | blue |
| 1 | 2 | fav_fruit | orange |
| 2 | 3 | fav_sport | hockey |
I want to be able to use a single query with a survey ID to query the rows by survey ID like so:
...where survey_id = 1
| survey_id | user_id | fav_color | fav_fruit |
|---|---|---|---|
| 1 | 1 | red | NULL |
| 1 | 2 | blue | orange |
...where survey_id = 2
| survey_id | user_id | fav_sport |
|---|---|---|
| 2 | 3 | hockey |
How can I perform a query to get each question for a survey as a column, listing each response under it?
CodePudding user response：
the tablefunc module contains function to do that: https://www.postgresql.org/docs/13/tablefunc.html
CodePudding user response：
Table:
create table survey(survey_id int ,user_id int,question varchar(20),answer varchar(20));
insert into survey values(1,1,'fav_colour','red'),(1,2,'fav_colour','blue'),(1,2,'fav_fruit','orange'),(2,3,'fav_sport','hockey');
SQL for Full Display:
WITH favcolor
AS (SELECT *
FROM (SELECT survey_id,
user_id,
CASE
WHEN question = 'fav_colour' THEN answer
END AS fav_color
FROM survey) inline_view
WHERE fav_color IS NOT NULL),
favsport
AS (SELECT *
FROM (SELECT survey_id,
user_id,
CASE
WHEN question = 'fav_sport' THEN answer
END AS fav_sport
FROM survey) inline_view
WHERE fav_sport IS NOT NULL),
favfruit
AS (SELECT *
FROM (SELECT survey_id,
user_id,
CASE
WHEN question = 'fav_fruit' THEN answer
END AS fav_fruit
FROM survey) inline_view
WHERE fav_fruit IS NOT NULL)
SELECT COALESCE(inline_view.survey_id, ff.survey_id) survey_id,
COALESCE(inline_view.user_id, ff.user_id) user_id,
inline_view.fav_color,
inline_view.fav_sport,
ff.fav_fruit
FROM (SELECT COALESCE(fc.survey_id, fs.survey_id) survey_id,
COALESCE(fc.user_id, fs.user_id) user_id,
fc.fav_color,
fs.fav_sport
FROM (SELECT survey_id,
user_id,
fav_color
FROM favcolor) fc
FULL OUTER JOIN (SELECT survey_id,
user_id,
fav_sport
FROM favsport) fs
ON fc.survey_id = fs.survey_id
AND fc.user_id = fs.user_id) inline_view
FULL OUTER JOIN (SELECT survey_id,
user_id,
fav_fruit
FROM favfruit) ff
ON inline_view.survey_id = ff.survey_id
AND inline_view.user_id = ff.user_id;
Output:
survey_id | user_id | fav_color | fav_sport | fav_fruit
----------- --------- ----------- ----------- -----------
1 | 1 | red | |
1 | 2 | blue | | orange
2 | 3 | | hockey |
(3 rows)
SQL for particular id , survey_id say 1 , the earlier SQL with filter on survery_id:
WITH favcolor
AS (SELECT *
FROM (SELECT survey_id,
user_id,
CASE
WHEN question = 'fav_colour' THEN answer
END AS fav_color
FROM survey) inline_view
WHERE fav_color IS NOT NULL),
favsport
AS (SELECT *
FROM (SELECT survey_id,
user_id,
CASE
WHEN question = 'fav_sport' THEN answer
END AS fav_sport
FROM survey) inline_view
WHERE fav_sport IS NOT NULL),
favfruit
AS (SELECT *
FROM (SELECT survey_id,
user_id,
CASE
WHEN question = 'fav_fruit' THEN answer
END AS fav_fruit
FROM survey) inline_view
WHERE fav_fruit IS NOT NULL)
SELECT *
FROM (SELECT COALESCE(inline_view.survey_id, ff.survey_id) survey_id,
COALESCE(inline_view.user_id, ff.user_id) user_id,
inline_view.fav_color,
inline_view.fav_sport,
ff.fav_fruit
FROM (SELECT COALESCE(fc.survey_id, fs.survey_id) survey_id,
COALESCE(fc.user_id, fs.user_id) user_id,
fc.fav_color,
fs.fav_sport
FROM (SELECT survey_id,
user_id,
fav_color
FROM favcolor) fc
FULL OUTER JOIN (SELECT survey_id,
user_id,
fav_sport
FROM favsport) fs
ON fc.survey_id = fs.survey_id
AND fc.user_id = fs.user_id) inline_view
FULL OUTER JOIN (SELECT survey_id,
user_id,
fav_fruit
FROM favfruit) ff
ON inline_view.survey_id = ff.survey_id
AND inline_view.user_id = ff.user_id)
final_inline_view
WHERE survey_id = 1 ;
Output:
survey_id | user_id | fav_color | fav_sport | fav_fruit
----------- --------- ----------- ----------- -----------
1 | 1 | red | |
1 | 2 | blue | | orange
(2 rows)
For Survey_id=2:
WITH favcolor
AS (SELECT *
FROM (SELECT survey_id,
user_id,
CASE
WHEN question = 'fav_colour' THEN answer
END AS fav_color
FROM survey) inline_view
WHERE fav_color IS NOT NULL),
favsport
AS (SELECT *
FROM (SELECT survey_id,
user_id,
CASE
WHEN question = 'fav_sport' THEN answer
END AS fav_sport
FROM survey) inline_view
WHERE fav_sport IS NOT NULL),
favfruit
AS (SELECT *
FROM (SELECT survey_id,
user_id,
CASE
WHEN question = 'fav_fruit' THEN answer
END AS fav_fruit
FROM survey) inline_view
WHERE fav_fruit IS NOT NULL)
SELECT *
FROM (SELECT COALESCE(inline_view.survey_id, ff.survey_id) survey_id,
COALESCE(inline_view.user_id, ff.user_id) user_id,
inline_view.fav_color,
inline_view.fav_sport,
ff.fav_fruit
FROM (SELECT COALESCE(fc.survey_id, fs.survey_id) survey_id,
COALESCE(fc.user_id, fs.user_id) user_id,
fc.fav_color,
fs.fav_sport
FROM (SELECT survey_id,
user_id,
fav_color
FROM favcolor) fc
FULL OUTER JOIN (SELECT survey_id,
user_id,
fav_sport
FROM favsport) fs
ON fc.survey_id = fs.survey_id
AND fc.user_id = fs.user_id) inline_view
FULL OUTER JOIN (SELECT survey_id,
user_id,
fav_fruit
FROM favfruit) ff
ON inline_view.survey_id = ff.survey_id
AND inline_view.user_id = ff.user_id)
final_inline_view
WHERE survey_id = 2 ;
Output:
survey_id | user_id | fav_color | fav_sport | fav_fruit
----------- --------- ----------- ----------- -----------
2 | 3 | | hockey |
(1 row)