0
I have this database of questions and answers
And I need to create a view that the questions and answers are arranged that way.
.
A friend of mine said something about an anchor but I can’t get it to work
1 answer
2
I have this database of questions and answers
And I need to create a view that the questions and answers are arranged that way.
Lucas, please tell us which database manager you are using.
As the number of questions and answers is fixed, here is the suggestion that uses the classic pivot:
-- código #1
SELECT id_entrevistado,
1 as [pergunta 1],
max(case when id_pergunta = 1 then resposta end) as [resposta 1],
2 as [pergunta 2],
max(case when id_pergunta = 2 then resposta end) as [resposta 2],
... acrescentar bloco de 3 a 11
12 as [pergunta 12],
max(case when id_pergunta = 12 then resposta end) as [resposta 12]
from Perguntas_Respostas
group by id_entrevistado;
Supplement the code for questions 3 to 11; just copy the block below, replacing n by the number of the question/answer.
n as [pergunta n],
max(case when id_pergunta = n then resposta end) as [resposta n],
-
Too much work for questionable results. The template you proposed is much better than what I had imagined, but the question as presented recalls much more the XY problem.
-
1
@Jeffersonquesado Good afternoon. I tried to respond literally to Lucas' request, because I don’t know how Qlik Sense needs the data. But in principle the column [question n] seems to me useless...
-
So guys, I had understood the problem wrong, but it was essential to solve the excerpt @Josédiz answered to solve the problem, Thank you all.
Browser other questions tagged sql
You are not signed in. Login or sign up in order to post.
Which database you are using
– Jorge Costa
What is your objective with this consultation? The more questions/answers you have the more columns you will have... if you have 500 questions/answers you will have 1,000 more columns!
– João Martins
There are 12 questions, they are the results of a survey of service satisfaction
– Lucas Pacheco
Will the result be consumed fully within sql? Or it can be printed by an external system?
– Jefferson Quesado
Totally within sql, I thought of making a script in java to process the table q I wanted, but, every update of the database would have q be processed by this script and is not feasible .
– Lucas Pacheco
This format does not seem readable to people, and is also not useful to be applied in reports... so it makes no sense to want the data like this. What is your goal ?
– Rovann Linhalis
This feature we call "pivot table", but in your case it seems to me that you are more denormalizing the table with this result, and just as @Rovannlinhalis mentioned it seems meaningless
– Ricardo Pontual
Possible duplicate of Convert row to column
– rLinhares
Actually I think it’s more correct that you say what you want to do with the result, so we can suggest better solutions...
– Sorack
My advisor asked me to create this view using sql so that I can later create a Dashboard using Qlik sense, as the data is constantly updated, using a script is unfeasible because of the processing cost, and the example I showed you is simple, there are other columns that have the question text and other information. I learned about this pivot table, and I found this https://www.devmedia.com.br/pivotando-dados-no-sql-part-01/9724 but it does not satisfy me much since I can not separate into columns.
– Lucas Pacheco
I believe that using INNER JOINS and Subselection manages one way, however, leaves the request too big and complicated and I’m not being happy, and I don’t even know if it’s really possible.
– Lucas Pacheco