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

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
– geetika, Commented Aug 4, 2019 at 1: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
– S-Man, Commented Aug 4, 2019 at 7: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
– S-Man, Commented Aug 4, 2019 at 7: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
– geetika, Commented Aug 4, 2019 at 16:50

S-Man · Accepted Answer · 2019-08-04 07:35:42Z

3

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

answered Aug 4, 2019 at 7:35

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

geetika · Accepted Answer · 2019-08-04 16:54:34Z

0

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
)
);

edited Aug 4, 2019 at 16:54

user11044402

answered Aug 4, 2019 at 16:53

geetika

291 silver badge2 bronze badges

Collectives™ on Stack Overflow

Transpose rows to columns in postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related