Dynamic Columns Pivot returning multiple rows - SQL Server 2014
up vote
1
down vote
favorite
I am trying to write a PIVOT query on dynamic columns. It works but it is returning multiple rows.
My query is
set @query = 'select PanelID, PanelCode, PanelName, EvaluatorID, PersonID, ApplicationID, ' + @cols + ' from (
select
distinct p.PanelID, p.PanelCode, p.PanelName, e.EvaluatorID, e.PersonID, ppl.LastName, pApps.ApplicationID, ev.AnswerNumeric
from
tblpanels p inner join tblEvaluators e on p.PanelID = e.PanelID
inner join tblPeople ppl on ppl.PersonID = e.PersonID
inner join tblPanelApps pApps on pApps.PanelID = p.PanelID
inner join tblEvaluations ev on ev.ApplicationID = pApps.ApplicationID and ev.EvaluatorID = e.EvaluatorID
where
p.PanelID in (1234, 3656)
)tmp
PIVOT
(
max(AnswerNumeric)
For LastName IN (' + @cols + ')
) As P
Order By PanelID, ApplicationID'
execute(@query)
The output is coming as
+=========+===========+===========+=============+==========+===============+======+=======+
| PanelID | PanelCode | PanelName | EvaluatorID | PersonID | ApplicationID | John | Carol |
+=========+===========+===========+=============+==========+===============+======+=======+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | 6 | NULL |
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | NULL | 4 |
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
It should be one row only per ApplicationID. How can I fix this query for below output?
+=========+===========+===========+=============+==========+===============+======+=======+
| PanelID | PanelCode | PanelName | EvaluatorID | PersonID | ApplicationID | John | Carol | Total
+=========+===========+===========+=============+==========+===============+======+=======+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | 6 | 4 | 10
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
EDIT: I also need a total score at the end for each row.
sql
sql-server tsql pivot asked Nov 20 at 16:07
JackSmith
164
|
show 1 more comment
up vote
1
down vote
favorite
I am trying to write a PIVOT query on dynamic columns. It works but it is returning multiple rows.
My query is
set @query = 'select PanelID, PanelCode, PanelName, EvaluatorID, PersonID, ApplicationID, ' + @cols + ' from (
select
distinct p.PanelID, p.PanelCode, p.PanelName, e.EvaluatorID, e.PersonID, ppl.LastName, pApps.ApplicationID, ev.AnswerNumeric
from
tblpanels p inner join tblEvaluators e on p.PanelID = e.PanelID
inner join tblPeople ppl on ppl.PersonID = e.PersonID
inner join tblPanelApps pApps on pApps.PanelID = p.PanelID
inner join tblEvaluations ev on ev.ApplicationID = pApps.ApplicationID and ev.EvaluatorID = e.EvaluatorID
where
p.PanelID in (1234, 3656)
)tmp
PIVOT
(
max(AnswerNumeric)
For LastName IN (' + @cols + ')
) As P
Order By PanelID, ApplicationID'
execute(@query)
The output is coming as
+=========+===========+===========+=============+==========+===============+======+=======+
| PanelID | PanelCode | PanelName | EvaluatorID | PersonID | ApplicationID | John | Carol |
+=========+===========+===========+=============+==========+===============+======+=======+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | 6 | NULL |
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | NULL | 4 |
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
It should be one row only per ApplicationID. How can I fix this query for below output?
+=========+===========+===========+=============+==========+===============+======+=======+
| PanelID | PanelCode | PanelName | EvaluatorID | PersonID | ApplicationID | John | Carol | Total
+=========+===========+===========+=============+==========+===============+======+=======+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | 6 | 4 | 10
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
EDIT: I also need a total score at the end for each row.
sql
sql-server tsql pivot asked Nov 20 at 16:07
JackSmith
164
I cannot format the display properly.
– JackSmith
Nov 20 at 16:08
You probably have strings that look the same but are different.
– Gordon Linoff
Nov 20 at 16:10
remove, ev.AnswerNumericcolumn in the sub select and try again
– Hiten004
Nov 20 at 16:11
I get an error - Invalid column name 'AnswerNumeric'. In the PIVOT we are using AnswerNumeric
– JackSmith
Nov 20 at 16:12
I also need a Total Score. Sum of all the values of the dynamic columns
– JackSmith
Nov 20 at 17:34
|
show 1 more comment
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I am trying to write a PIVOT query on dynamic columns. It works but it is returning multiple rows.
My query is
set @query = 'select PanelID, PanelCode, PanelName, EvaluatorID, PersonID, ApplicationID, ' + @cols + ' from (
select
distinct p.PanelID, p.PanelCode, p.PanelName, e.EvaluatorID, e.PersonID, ppl.LastName, pApps.ApplicationID, ev.AnswerNumeric
from
tblpanels p inner join tblEvaluators e on p.PanelID = e.PanelID
inner join tblPeople ppl on ppl.PersonID = e.PersonID
inner join tblPanelApps pApps on pApps.PanelID = p.PanelID
inner join tblEvaluations ev on ev.ApplicationID = pApps.ApplicationID and ev.EvaluatorID = e.EvaluatorID
where
p.PanelID in (1234, 3656)
)tmp
PIVOT
(
max(AnswerNumeric)
For LastName IN (' + @cols + ')
) As P
Order By PanelID, ApplicationID'
execute(@query)
The output is coming as
+=========+===========+===========+=============+==========+===============+======+=======+
| PanelID | PanelCode | PanelName | EvaluatorID | PersonID | ApplicationID | John | Carol |
+=========+===========+===========+=============+==========+===============+======+=======+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | 6 | NULL |
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | NULL | 4 |
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
It should be one row only per ApplicationID. How can I fix this query for below output?
+=========+===========+===========+=============+==========+===============+======+=======+
| PanelID | PanelCode | PanelName | EvaluatorID | PersonID | ApplicationID | John | Carol | Total
+=========+===========+===========+=============+==========+===============+======+=======+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | 6 | 4 | 10
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
EDIT: I also need a total score at the end for each row.
sql
sql-server tsql pivot asked Nov 20 at 16:07
JackSmith
164
I am trying to write a PIVOT query on dynamic columns. It works but it is returning multiple rows.
My query is
set @query = 'select PanelID, PanelCode, PanelName, EvaluatorID, PersonID, ApplicationID, ' + @cols + ' from (
select
distinct p.PanelID, p.PanelCode, p.PanelName, e.EvaluatorID, e.PersonID, ppl.LastName, pApps.ApplicationID, ev.AnswerNumeric
from
tblpanels p inner join tblEvaluators e on p.PanelID = e.PanelID
inner join tblPeople ppl on ppl.PersonID = e.PersonID
inner join tblPanelApps pApps on pApps.PanelID = p.PanelID
inner join tblEvaluations ev on ev.ApplicationID = pApps.ApplicationID and ev.EvaluatorID = e.EvaluatorID
where
p.PanelID in (1234, 3656)
)tmp
PIVOT
(
max(AnswerNumeric)
For LastName IN (' + @cols + ')
) As P
Order By PanelID, ApplicationID'
execute(@query)
The output is coming as
+=========+===========+===========+=============+==========+===============+======+=======+
| PanelID | PanelCode | PanelName | EvaluatorID | PersonID | ApplicationID | John | Carol |
+=========+===========+===========+=============+==========+===============+======+=======+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | 6 | NULL |
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | NULL | 4 |
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
It should be one row only per ApplicationID. How can I fix this query for below output?
+=========+===========+===========+=============+==========+===============+======+=======+
| PanelID | PanelCode | PanelName | EvaluatorID | PersonID | ApplicationID | John | Carol | Total
+=========+===========+===========+=============+==========+===============+======+=======+
| 1234 | 12123412 | Panel A | 3674 | 4834 | 112233 | 6 | 4 | 10
+---------+-----------+-----------+-------------+----------+---------------+------+-------+
EDIT: I also need a total score at the end for each row.
sql
sql-server tsql pivot sql
sql-server tsql pivot asked Nov 20 at 16:07
JackSmith
164
asked Nov 20 at 16:07
JackSmith
164
edited Nov 20 at 17:34
asked Nov 20 at 16:07
JackSmith
164
asked Nov 20 at 16:07
JackSmith
164
asked Nov 20 at 16:07
JackSmith
164
164
I cannot format the display properly.
– JackSmith
Nov 20 at 16:08
You probably have strings that look the same but are different.
– Gordon Linoff
Nov 20 at 16:10
remove, ev.AnswerNumericcolumn in the sub select and try again
– Hiten004
Nov 20 at 16:11
I get an error - Invalid column name 'AnswerNumeric'. In the PIVOT we are using AnswerNumeric
– JackSmith
Nov 20 at 16:12
I also need a Total Score. Sum of all the values of the dynamic columns
– JackSmith
Nov 20 at 17:34
|
show 1 more comment
I cannot format the display properly.
– JackSmith
Nov 20 at 16:08
You probably have strings that look the same but are different.
– Gordon Linoff
Nov 20 at 16:10
remove, ev.AnswerNumericcolumn in the sub select and try again
– Hiten004
Nov 20 at 16:11
I get an error - Invalid column name 'AnswerNumeric'. In the PIVOT we are using AnswerNumeric
– JackSmith
Nov 20 at 16:12
I also need a Total Score. Sum of all the values of the dynamic columns
– JackSmith
Nov 20 at 17:34
I cannot format the display properly.
– JackSmith
Nov 20 at 16:08
I cannot format the display properly.
– JackSmith
Nov 20 at 16:08
You probably have strings that look the same but are different.
– Gordon Linoff
Nov 20 at 16:10
You probably have strings that look the same but are different.
– Gordon Linoff
Nov 20 at 16:10
remove
, ev.AnswerNumeric column in the sub select and try again– Hiten004
Nov 20 at 16:11
remove
, ev.AnswerNumeric column in the sub select and try again– Hiten004
Nov 20 at 16:11
I get an error - Invalid column name 'AnswerNumeric'. In the PIVOT we are using AnswerNumeric
– JackSmith
Nov 20 at 16:12
I get an error - Invalid column name 'AnswerNumeric'. In the PIVOT we are using AnswerNumeric
– JackSmith
Nov 20 at 16:12
I also need a Total Score. Sum of all the values of the dynamic columns
– JackSmith
Nov 20 at 17:34
I also need a Total Score. Sum of all the values of the dynamic columns
– JackSmith
Nov 20 at 17:34
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
I fixed it. The evaluatorID and the PersonID were causing the issue. I took them off from the subquery and it is now fixed. I also added the Total column. The updated query is
set @query = 'select PanelID, PanelCode, PanelName, ApplicationID,
' + @cols + ', (ISNULL(' + REPLACE(@cols, ',', ',0.) + ISNULL(') + ',0.)) AS TOTAL from (
select
distinct p.PanelID, p.PanelCode, p.PanelName, pApps.ApplicationID, ppl.LastName, isnull(ev.AnswerNumeric, 0) as AnswerNumeric
from
tblpanels p inner join tblEvaluators e on p.PanelID = e.PanelID
inner join tblPeople ppl on ppl.PersonID = e.PersonID
inner join tblPanelApps pApps on pApps.PanelID = p.PanelID
left join tblEvaluations ev on ev.ApplicationID = pApps.ApplicationID and ev.EvaluatorID = e.EvaluatorID
where
p.PanelID in (1234, 3656)
)tmp
PIVOT
(
max(AnswerNumeric)
For LastName IN (' + @cols + ')
) As P
Order By PanelID, ApplicationID'
--print @query
execute(@query)
answered Nov 21 at 16:11
JackSmith
164
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53397025%2fdynamic-columns-pivot-returning-multiple-rows-sql-server-2014%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
I fixed it. The evaluatorID and the PersonID were causing the issue. I took them off from the subquery and it is now fixed. I also added the Total column. The updated query is
set @query = 'select PanelID, PanelCode, PanelName, ApplicationID,
' + @cols + ', (ISNULL(' + REPLACE(@cols, ',', ',0.) + ISNULL(') + ',0.)) AS TOTAL from (
select
distinct p.PanelID, p.PanelCode, p.PanelName, pApps.ApplicationID, ppl.LastName, isnull(ev.AnswerNumeric, 0) as AnswerNumeric
from
tblpanels p inner join tblEvaluators e on p.PanelID = e.PanelID
inner join tblPeople ppl on ppl.PersonID = e.PersonID
inner join tblPanelApps pApps on pApps.PanelID = p.PanelID
left join tblEvaluations ev on ev.ApplicationID = pApps.ApplicationID and ev.EvaluatorID = e.EvaluatorID
where
p.PanelID in (1234, 3656)
)tmp
PIVOT
(
max(AnswerNumeric)
For LastName IN (' + @cols + ')
) As P
Order By PanelID, ApplicationID'
--print @query
execute(@query)
answered Nov 21 at 16:11
JackSmith
164
add a comment |
up vote
0
down vote
accepted
I fixed it. The evaluatorID and the PersonID were causing the issue. I took them off from the subquery and it is now fixed. I also added the Total column. The updated query is
set @query = 'select PanelID, PanelCode, PanelName, ApplicationID,
' + @cols + ', (ISNULL(' + REPLACE(@cols, ',', ',0.) + ISNULL(') + ',0.)) AS TOTAL from (
select
distinct p.PanelID, p.PanelCode, p.PanelName, pApps.ApplicationID, ppl.LastName, isnull(ev.AnswerNumeric, 0) as AnswerNumeric
from
tblpanels p inner join tblEvaluators e on p.PanelID = e.PanelID
inner join tblPeople ppl on ppl.PersonID = e.PersonID
inner join tblPanelApps pApps on pApps.PanelID = p.PanelID
left join tblEvaluations ev on ev.ApplicationID = pApps.ApplicationID and ev.EvaluatorID = e.EvaluatorID
where
p.PanelID in (1234, 3656)
)tmp
PIVOT
(
max(AnswerNumeric)
For LastName IN (' + @cols + ')
) As P
Order By PanelID, ApplicationID'
--print @query
execute(@query)
answered Nov 21 at 16:11
JackSmith
164
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
I fixed it. The evaluatorID and the PersonID were causing the issue. I took them off from the subquery and it is now fixed. I also added the Total column. The updated query is
set @query = 'select PanelID, PanelCode, PanelName, ApplicationID,
' + @cols + ', (ISNULL(' + REPLACE(@cols, ',', ',0.) + ISNULL(') + ',0.)) AS TOTAL from (
select
distinct p.PanelID, p.PanelCode, p.PanelName, pApps.ApplicationID, ppl.LastName, isnull(ev.AnswerNumeric, 0) as AnswerNumeric
from
tblpanels p inner join tblEvaluators e on p.PanelID = e.PanelID
inner join tblPeople ppl on ppl.PersonID = e.PersonID
inner join tblPanelApps pApps on pApps.PanelID = p.PanelID
left join tblEvaluations ev on ev.ApplicationID = pApps.ApplicationID and ev.EvaluatorID = e.EvaluatorID
where
p.PanelID in (1234, 3656)
)tmp
PIVOT
(
max(AnswerNumeric)
For LastName IN (' + @cols + ')
) As P
Order By PanelID, ApplicationID'
--print @query
execute(@query)
answered Nov 21 at 16:11
JackSmith
164
I fixed it. The evaluatorID and the PersonID were causing the issue. I took them off from the subquery and it is now fixed. I also added the Total column. The updated query is
set @query = 'select PanelID, PanelCode, PanelName, ApplicationID,
' + @cols + ', (ISNULL(' + REPLACE(@cols, ',', ',0.) + ISNULL(') + ',0.)) AS TOTAL from (
select
distinct p.PanelID, p.PanelCode, p.PanelName, pApps.ApplicationID, ppl.LastName, isnull(ev.AnswerNumeric, 0) as AnswerNumeric
from
tblpanels p inner join tblEvaluators e on p.PanelID = e.PanelID
inner join tblPeople ppl on ppl.PersonID = e.PersonID
inner join tblPanelApps pApps on pApps.PanelID = p.PanelID
left join tblEvaluations ev on ev.ApplicationID = pApps.ApplicationID and ev.EvaluatorID = e.EvaluatorID
where
p.PanelID in (1234, 3656)
)tmp
PIVOT
(
max(AnswerNumeric)
For LastName IN (' + @cols + ')
) As P
Order By PanelID, ApplicationID'
--print @query
execute(@query)
answered Nov 21 at 16:11
JackSmith
164
answered Nov 21 at 16:11
JackSmith
164
answered Nov 21 at 16:11
JackSmith
164
answered Nov 21 at 16:11
JackSmith
164
164
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53397025%2fdynamic-columns-pivot-returning-multiple-rows-sql-server-2014%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I cannot format the display properly.
– JackSmith
Nov 20 at 16:08
You probably have strings that look the same but are different.
– Gordon Linoff
Nov 20 at 16:10
remove
, ev.AnswerNumericcolumn in the sub select and try again– Hiten004
Nov 20 at 16:11
I get an error - Invalid column name 'AnswerNumeric'. In the PIVOT we are using AnswerNumeric
– JackSmith
Nov 20 at 16:12
I also need a Total Score. Sum of all the values of the dynamic columns
– JackSmith
Nov 20 at 17:34