Transpose rows to columns in postgres

Question

I need to transform rows to columns with the corresponding values

I am new to Postgres, I have inserted the link to images of my table and how I need the Required result. I will really appreciate any suggestions or remarks to resolve it enter image description here:

original table

required result

you can refer https://stackoverflow.com/questions/23060256/postgres-transpose-rows-to-columns — RamSharma, Aug 04 '19 at 00:35
my scenario is different, I need my row values to be column names which are not covered in this example. ex- currently i have columns as submission id,question id,question type,answers. when I transpose it I want the question type to become different columns and contain answers as a value against it — geetika, Aug 04 '19 at 01:14
1.Please do not use images to describe your problem. You want us to help you. But now we cannot copy/paste your data. That creates a load more work for us. Furthermore, please simplify your problem. To describe your problem you do not need a bunch of unique ids, so much data sets and so on. 5 records with question_types and a simple integer id would be lovely. — S-Man, Aug 04 '19 at 07:28
3. Concerning the problem: You want the values of question_type to be the columns. Are the values fixed or dynamic (in other words: Are there exactly these 8 values?) — S-Man, Aug 04 '19 at 07:29
i understad that using image is problem but i was not able to type in everything and was not able to add excel sheet for refrence thats why i went for image.I appreciate everyone helping out. though i was able to resolve the issue,here is the query i wrote :- — geetika, Aug 04 '19 at 16:50
Your question is quite unclear. Your problem and desired output looks same. It would better if you have described if more clearly along with clear and readable examples. — anil shrestha, Jun 15 '20 at 04:38

score 3 · Answer 1 · answered Aug 04 '19 at 07:35

In case that the columns stay the same, this is a simple pivot. This can be done by grouping and filtering the aggregates:

demo:db<>fiddle

SELECT
    submission_id, question_id, question_type,
    MAX(answer) FILTER (WHERE question_type = 'A') AS answer_to_a,
    MAX(answer) FILTER (WHERE question_type = 'B') AS answer_to_b,
    MAX(answer) FILTER (WHERE question_type = 'C') AS answer_to_c
FROM
    questions
GROUP BY submission_id, question_id, question_type

score 0 · Answer 2 · edited Aug 04 '19 at 16:54

I understand that using an image is a problem but I was not able to type in everything and was not able to add excel sheet f. I appreciate everyone helping out. though I was able to resolve the issue,

create view test3 as 
( SELECT
    *
FROM CROSSTAB (
    'SELECT
  entryid
, submissionid
, questionid
, questiontext
, questiontype
, answer    
     FROM test2
     ORDER BY 1;'
     , $$ VALUES ('CSAT'::TEXT), ('FACILITIES'::TEXT), ('NPS'::TEXT), ('SERVICES'::TEXT), ('FOOD_BEVERAGES'::TEXT) $$
) AS "ct" (
    "entryid" TEXT
    , "submissionid" TEXT
, "questionid" TEXT
, "questiontext" TEXT
    , "CSAT" TEXT
    , "FACILITIES" TEXT
    , "NPS" TEXT
, "SERVICES" TEXT
, "FOOD_BEVERAGES" TEXT
)
);

Transpose rows to columns in postgres

2 Answers2