I
I
Ilya Magdenko2019-09-20 14:19:36
SQL
Ilya Magdenko, 2019-09-20 14:19:36

SQL Pivot is not working correctly, what am I doing wrong?

Hello,
SQL Pivot is not working as expected and I can't find the problem. Perhaps you can help.
Situation:
There is a database of, say, a questionnaire. The 3 columns I need are Person, Question and Answer (of course there are many more columns).
All this is stored in this form:
Person 1, Question 1, Answer to question 1
Person 1, Question 2, Answer to question 2
Person 1, Question 3, Answer to question 3
Person 2, Question 1, Answer to question 1
Person 2, Question 2, Answer to question 2

{ProcedureId, TokenValue, HtmlValue]
and so on.
Like this:
5d84b3b02f118313980847.png
I am trying to modify a SQL query and display it in Excel 2007 (there is a need for this particular version, on others it is implemented simply with power query)

Select ProcedureId, [3_1_explain_alert_system_and_boosters_required], [1_1_patient_blacklist_checked]
FROM 
(
    select * from ProcedureStepsDetails
) As Temp
PIVOT
(
    max([HtmlValue]) 
    FOR [TokenValue] In([3_1_explain_alert_system_and_boosters_required], [1_1_patient_blacklist_checked])
) As pt

According to the code - [3_1_explain_alert_system_and_boosters_required], [1_1_patient_blacklist_checked] is the ID of questions.
This code works relatively well, but I thought that it would display a person as 1 unit, like this:
Человек * Вопрос 1 * Вопрос 2 * Вопрос 3
Человек1  Ответ 1    Ответ 2    Ответ 3
Человек2  Ответ 1    null       Ответ 3

and he
Человек * Вопрос 1 * Вопрос 2 * Вопрос 3
Человек1  Ответ 1    null       null
Человек1  null       null       null
Человек1  null       null       Ответ 3
Человек1  null       null       null
Человек1  null       null       null

What do I need to change in this SQL Query?
I have been suffering for a long time, I can not understand the reason.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question