How to select multiple Integer data from different columns and merge them into single column of array integer

Question

I have table as follow in my POSTGRES database

Table name:userDetails

column 1: userId (Bigint)

column 2: questionId1 (int) Foreign key:: userQuestions(id)

column 3: answer1 (String)

column 4: questionId2 (int) Foreign key:: userQuestions(id)    |

column 5: answer2 (String)

Table name: userQuestions

column 1: id(bigint)

column 2: question(String)

and i want to select output as follow on the basis of userId,

column 1: userId (Bigint)

column 2: questionId's (int [])(questionId1 and questionId2)

column 3: questions (String [])( array of questions from table userQuestions against the the questionId1 and questionId2 )

column 4: answer(String []) (answer1 and answer2 from userDetails table in array of String)

please help me to write sql query.

SAMPLE DATA

Tablename :: userDetails

|userId         |questionId1        |answer1        |questionId2        |answer2

  abc                 1               "hp"                2               "tommy"

Tablename :: userQuestions

id          question 

1           "What is brand name of your laptop?"

2           "What is name of your pet?"

Expected OUTPUT::

userId          questionIds        answers                       questions

 abcd              [1,2]        ["hp","tommy"]              ["What is brand 
                                                              name of your 
                                                              laptop?",
                                                            "What is name of 
                                                             your pet?"]

@ Gordon i have edited my question and have added some sample data and expected output, can you help me in writing sql query accordingly — BZT
– BZT, Commented Aug 1, 2018 at 12:06
. . Something is wrong with your data structure. UserQuestions doesn't seem to have a connection to Users. I'm expecting a junction table, with one row per user and per question. — Gordon Linoff
– Gordon Linoff, Commented Aug 1, 2018 at 12:13
Yes their is relation @Gordon, the columns questionId1 and questionId2 from table userDetails has foreign key on column "id" of table "userQuestions" which i have mentioned above — BZT
– BZT, Commented Aug 2, 2018 at 4:25

Tito · Accepted Answer · 2018-08-03 05:33:55Z

1

I would put it differently

select d.userid, d.questionid1 || ',' || d.questionid2, d.answer1 || ' ' || d.answer2, 
        string_agg(q.question,',')
from userDetails d
join userQuestions q on q.id = d.questionid1 or q.id = d.questionid2
GROUP BY d.userid

edited Aug 3, 2018 at 5:33

answered Aug 1, 2018 at 22:24

Tito

6811 gold badge9 silver badges25 bronze badges

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

1 Comment

BZT Over a year ago

This one worked @Tito Thank you ,it only needs 'GROUP BY d.userid' at the end

Tonner Mààn · Accepted Answer · 2018-08-01 10:36:45Z

1

I think the only way is to insert the output in a temporary table, that way you can insert questionId1 with it's question string and answer1, then insert questionId2 with it's question string and answer2 to the same columns, and then show the results.

Edit: As you requested here is an example that works in SQL Server (I don't know about POSTGRES, never worked on it)

Select t1.userId, t1.questionId1 as questionId, (Select t2.question from userQuestions t2 where t2.id = t1.questionId1) as questions, answer1 as answer into #TempTable from userDetails
UNION ALL Select t1.userId, t1.questionId2 as questionId, (Select t2.question from userQuestions t2 where t2.id = t1.questionId2) as questions, answer2 as answer into #TempTable from userDetails
Select * from #TempTable

Don't forget before re-executing the code you'll have to delete the temporary table using Drop table #TempTable

I have tried a simillar code on SQL Server 2016 and it's working just fine.

edited Aug 1, 2018 at 10:36

answered Aug 1, 2018 at 9:33

Tonner Mààn

1331 silver badge11 bronze badges

2 Comments

BZT Over a year ago

@ Tonner Maan as i am new to sql can u please share the sql query for it

Tonner Mààn Over a year ago

@BZT there you go an example in SQL Server, not sure if postgresql uses the same queries but you can edit as you need to achieve your goal, hope it helps

Collectives™ on Stack Overflow

How to select multiple Integer data from different columns and merge them into single column of array integer

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related