How do I turn some rows into columns?

Asked

Viewed 82 times

0

I have this database of questions and answers

inserir a descrição da imagem aqui

And I need to create a view that the questions and answers are arranged that way.

.

inserir a descrição da imagem aqui

A friend of mine said something about an anchor but I can’t get it to work

sql
  • 2

    Which database you are using

  • 1

    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!

  • 1

    There are 12 questions, they are the results of a survey of service satisfaction

  • Will the result be consumed fully within sql? Or it can be printed by an external system?

  • 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 .

  • 1

    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 ?

  • 1

    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

  • 4

    Possible duplicate of Convert row to column

  • 1

    Actually I think it’s more correct that you say what you want to do with the result, so we can suggest better solutions...

  • 1

    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.

  • 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.

Show 6 more comments

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.