transpose multiple columns into one with pivot sql
0
I need transpose multiple columns into one specific column SQL ( my real example has multiple columns and rows). Please see picture for details with what I have so far. I have also attached the full code for temp table that I created.
I have tried to solve the problem separately and created so far two queries:
- one is to stack all columns under one desired via union all ( not sure how to create dynamic table because my real example has a large data and doing manual insert wouldn't the best idea);
- pivot the data based on criteria and summing the totals
I don't know how to sum all my data under newly created 'columns' column from query # stacking and not sure if it is even possible.
----created new tables : test and plan test
create table test (
plancode int,
amount int,
category varchar (255),
ownership varchar (255),
status varchar (255))
select * from test
insert into test (
plancode,
amount,
category,
ownership,
status)
values (5,100,'parttime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (5,100,'fulltime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (10,100,'fulltime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (10,10,'partime','partial', 'complete')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (15,100,'seasonal','full', 'complete')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (15,200,'partime','simple','complete')
create table plantest (
plancode int,
planname varchar (255))
insert into plantest (
plancode,
planname)
values (5, 'plan A')
insert into plantest (
plancode,
planname)
values (10, 'plan B')
insert into plantest (
plancode,
planname)
values (15, 'plan C')
-----------------------------------------------------------------------------
Temp tables are created below
select * from test
select * from plantest
________________________________________________________________
This #1 query is for union all.
select plancode,
category as columns from test
union all
select plancode, ownership from test
union all
select plancode, status from test
This #2 query is for pivot all.
---pivoting on one of the columns, 'category' but I need to include all the other columns 'ownership' and 'status' right under the last value of 'category' column in this query
select * from (
select
category, ---only including one column.. that is why I thought if I stack all data under values here that it should work but right now it is not stacked.
amount,
plancode
from test) as tbl
pivot (sum(amount) for plancode in ([5],[15],[10])) as pivottable
transpose
edited Jan 18 at 21:24
Jason Aller
3,06192932
asked Jan 16 at 20:23
SQLLearnerSQLLearner
12
add a comment |
0
I need transpose multiple columns into one specific column SQL ( my real example has multiple columns and rows). Please see picture for details with what I have so far. I have also attached the full code for temp table that I created.
I have tried to solve the problem separately and created so far two queries:
- one is to stack all columns under one desired via union all ( not sure how to create dynamic table because my real example has a large data and doing manual insert wouldn't the best idea);
- pivot the data based on criteria and summing the totals
I don't know how to sum all my data under newly created 'columns' column from query # stacking and not sure if it is even possible.
----created new tables : test and plan test
create table test (
plancode int,
amount int,
category varchar (255),
ownership varchar (255),
status varchar (255))
select * from test
insert into test (
plancode,
amount,
category,
ownership,
status)
values (5,100,'parttime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (5,100,'fulltime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (10,100,'fulltime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (10,10,'partime','partial', 'complete')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (15,100,'seasonal','full', 'complete')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (15,200,'partime','simple','complete')
create table plantest (
plancode int,
planname varchar (255))
insert into plantest (
plancode,
planname)
values (5, 'plan A')
insert into plantest (
plancode,
planname)
values (10, 'plan B')
insert into plantest (
plancode,
planname)
values (15, 'plan C')
-----------------------------------------------------------------------------
Temp tables are created below
select * from test
select * from plantest
________________________________________________________________
This #1 query is for union all.
select plancode,
category as columns from test
union all
select plancode, ownership from test
union all
select plancode, status from test
This #2 query is for pivot all.
---pivoting on one of the columns, 'category' but I need to include all the other columns 'ownership' and 'status' right under the last value of 'category' column in this query
select * from (
select
category, ---only including one column.. that is why I thought if I stack all data under values here that it should work but right now it is not stacked.
amount,
plancode
from test) as tbl
pivot (sum(amount) for plancode in ([5],[15],[10])) as pivottable
transpose
edited Jan 18 at 21:24
Jason Aller
3,06192932
asked Jan 16 at 20:23
SQLLearnerSQLLearner
12
add a comment |
0
0
0
I need transpose multiple columns into one specific column SQL ( my real example has multiple columns and rows). Please see picture for details with what I have so far. I have also attached the full code for temp table that I created.
I have tried to solve the problem separately and created so far two queries:
- one is to stack all columns under one desired via union all ( not sure how to create dynamic table because my real example has a large data and doing manual insert wouldn't the best idea);
- pivot the data based on criteria and summing the totals
I don't know how to sum all my data under newly created 'columns' column from query # stacking and not sure if it is even possible.
----created new tables : test and plan test
create table test (
plancode int,
amount int,
category varchar (255),
ownership varchar (255),
status varchar (255))
select * from test
insert into test (
plancode,
amount,
category,
ownership,
status)
values (5,100,'parttime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (5,100,'fulltime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (10,100,'fulltime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (10,10,'partime','partial', 'complete')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (15,100,'seasonal','full', 'complete')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (15,200,'partime','simple','complete')
create table plantest (
plancode int,
planname varchar (255))
insert into plantest (
plancode,
planname)
values (5, 'plan A')
insert into plantest (
plancode,
planname)
values (10, 'plan B')
insert into plantest (
plancode,
planname)
values (15, 'plan C')
-----------------------------------------------------------------------------
Temp tables are created below
select * from test
select * from plantest
________________________________________________________________
This #1 query is for union all.
select plancode,
category as columns from test
union all
select plancode, ownership from test
union all
select plancode, status from test
This #2 query is for pivot all.
---pivoting on one of the columns, 'category' but I need to include all the other columns 'ownership' and 'status' right under the last value of 'category' column in this query
select * from (
select
category, ---only including one column.. that is why I thought if I stack all data under values here that it should work but right now it is not stacked.
amount,
plancode
from test) as tbl
pivot (sum(amount) for plancode in ([5],[15],[10])) as pivottable
transpose
edited Jan 18 at 21:24
Jason Aller
3,06192932
asked Jan 16 at 20:23
SQLLearnerSQLLearner
12
I need transpose multiple columns into one specific column SQL ( my real example has multiple columns and rows). Please see picture for details with what I have so far. I have also attached the full code for temp table that I created.
I have tried to solve the problem separately and created so far two queries:
- one is to stack all columns under one desired via union all ( not sure how to create dynamic table because my real example has a large data and doing manual insert wouldn't the best idea);
- pivot the data based on criteria and summing the totals
I don't know how to sum all my data under newly created 'columns' column from query # stacking and not sure if it is even possible.
----created new tables : test and plan test
create table test (
plancode int,
amount int,
category varchar (255),
ownership varchar (255),
status varchar (255))
select * from test
insert into test (
plancode,
amount,
category,
ownership,
status)
values (5,100,'parttime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (5,100,'fulltime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (10,100,'fulltime','simple', 'inprogress')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (10,10,'partime','partial', 'complete')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (15,100,'seasonal','full', 'complete')
insert into test (
plancode,
amount,
category,
ownership,
status)
values (15,200,'partime','simple','complete')
create table plantest (
plancode int,
planname varchar (255))
insert into plantest (
plancode,
planname)
values (5, 'plan A')
insert into plantest (
plancode,
planname)
values (10, 'plan B')
insert into plantest (
plancode,
planname)
values (15, 'plan C')
-----------------------------------------------------------------------------
Temp tables are created below
select * from test
select * from plantest
________________________________________________________________
This #1 query is for union all.
select plancode,
category as columns from test
union all
select plancode, ownership from test
union all
select plancode, status from test
This #2 query is for pivot all.
---pivoting on one of the columns, 'category' but I need to include all the other columns 'ownership' and 'status' right under the last value of 'category' column in this query
select * from (
select
category, ---only including one column.. that is why I thought if I stack all data under values here that it should work but right now it is not stacked.
amount,
plancode
from test) as tbl
pivot (sum(amount) for plancode in ([5],[15],[10])) as pivottable
transpose
transpose
edited Jan 18 at 21:24
Jason Aller
3,06192932
asked Jan 16 at 20:23
SQLLearnerSQLLearner
12
edited Jan 18 at 21:24
Jason Aller
3,06192932
asked Jan 16 at 20:23
SQLLearnerSQLLearner
12
edited Jan 18 at 21:24
Jason Aller
3,06192932
edited Jan 18 at 21:24
Jason Aller
3,06192932
edited Jan 18 at 21:24
Jason Aller
3,06192932
3,06192932
asked Jan 16 at 20:23
SQLLearnerSQLLearner
12
asked Jan 16 at 20:23
SQLLearnerSQLLearner
12
asked Jan 16 at 20:23
SQLLearnerSQLLearner
12
12
add a comment |
add a comment |
0
active
oldest
votes
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%2f54224808%2ftranspose-multiple-columns-into-one-with-pivot-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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%2f54224808%2ftranspose-multiple-columns-into-one-with-pivot-sql%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
