How to create a PIVOT using two tables
1
I have two tables:
Table1:
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
| Category | Name | Goal | WK40 | WK41 | WK42 | WK43 | WK44 | WK 45 |
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
| Data | Data | Data | Data | Data | Data | Data | Data | Data |
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
Table2:
+-------+--------+--------+--------+--------+-------+
| W1 | W2 | W3 | W4 | W5 | W6 |
+-------+--------+--------+--------+--------+-------+
| WK40 | WK41 | WK42 | WK43 | WK44 | WK45 |
+-------+--------+--------+--------+--------+-------+
In Table 1, the WK40, WK41 etc. column names are dynamic (i.e., these column names change every week depending on the current week of the year & then shows the last 6 weeks). Table 1 has all the required data.
In Table 2, the column names are W1, W2, ...W6 and the column name here is static. But the data in each column (WK40, WK41....) changes every week based on the current week.
I want to create a table like this:
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
| Category | Name | Goal | W1 | W2 | W3 | W4 | W5 | W6 |
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
| Data | Data | Data | Data | Data | Data | Data | Data | Data |
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
I've never used PIVOT before so need help creating one for this. Thanks.
sql
sql-server tsql pivot edited Nov 19 '18 at 8:59
Andrea
7,661144652
asked Nov 14 '18 at 2:36
user10096621user10096621
4016
add a comment |
1
I have two tables:
Table1:
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
| Category | Name | Goal | WK40 | WK41 | WK42 | WK43 | WK44 | WK 45 |
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
| Data | Data | Data | Data | Data | Data | Data | Data | Data |
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
Table2:
+-------+--------+--------+--------+--------+-------+
| W1 | W2 | W3 | W4 | W5 | W6 |
+-------+--------+--------+--------+--------+-------+
| WK40 | WK41 | WK42 | WK43 | WK44 | WK45 |
+-------+--------+--------+--------+--------+-------+
In Table 1, the WK40, WK41 etc. column names are dynamic (i.e., these column names change every week depending on the current week of the year & then shows the last 6 weeks). Table 1 has all the required data.
In Table 2, the column names are W1, W2, ...W6 and the column name here is static. But the data in each column (WK40, WK41....) changes every week based on the current week.
I want to create a table like this:
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
| Category | Name | Goal | W1 | W2 | W3 | W4 | W5 | W6 |
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
| Data | Data | Data | Data | Data | Data | Data | Data | Data |
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
I've never used PIVOT before so need help creating one for this. Thanks.
sql
sql-server tsql pivot edited Nov 19 '18 at 8:59
Andrea
7,661144652
asked Nov 14 '18 at 2:36
user10096621user10096621
4016
3
it will help us to understand your situation better if you can add some realistic sample data and the expected result.Dataeverywhere doesn't help much
– Squirrel
Nov 14 '18 at 2:40
1
I would strongly suggest normalising your data rather than finding a quick-fix solution to this particular problem. If you normalise your data, you at least have the option of pivoting whichever way you want after it without having to figure out a silly work-around to ever-changing column names.
– ZLK
Nov 14 '18 at 2:59
I agree with ZLK. Normalization makes work like this so much simpler, and it keeps the solution dynamically able to add new columns on its own. Much easier than having to go back to an old solution to make it able to handle new data needs.
– Utrolig
Nov 14 '18 at 6:24
add a comment |
1
1
1
0
I have two tables:
Table1:
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
| Category | Name | Goal | WK40 | WK41 | WK42 | WK43 | WK44 | WK 45 |
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
| Data | Data | Data | Data | Data | Data | Data | Data | Data |
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
Table2:
+-------+--------+--------+--------+--------+-------+
| W1 | W2 | W3 | W4 | W5 | W6 |
+-------+--------+--------+--------+--------+-------+
| WK40 | WK41 | WK42 | WK43 | WK44 | WK45 |
+-------+--------+--------+--------+--------+-------+
In Table 1, the WK40, WK41 etc. column names are dynamic (i.e., these column names change every week depending on the current week of the year & then shows the last 6 weeks). Table 1 has all the required data.
In Table 2, the column names are W1, W2, ...W6 and the column name here is static. But the data in each column (WK40, WK41....) changes every week based on the current week.
I want to create a table like this:
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
| Category | Name | Goal | W1 | W2 | W3 | W4 | W5 | W6 |
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
| Data | Data | Data | Data | Data | Data | Data | Data | Data |
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
I've never used PIVOT before so need help creating one for this. Thanks.
sql
sql-server tsql pivot edited Nov 19 '18 at 8:59
Andrea
7,661144652
asked Nov 14 '18 at 2:36
user10096621user10096621
4016
I have two tables:
Table1:
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
| Category | Name | Goal | WK40 | WK41 | WK42 | WK43 | WK44 | WK 45 |
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
| Data | Data | Data | Data | Data | Data | Data | Data | Data |
+---------------+--------+--------+--------+--------+--------+--------+--------+--------+
Table2:
+-------+--------+--------+--------+--------+-------+
| W1 | W2 | W3 | W4 | W5 | W6 |
+-------+--------+--------+--------+--------+-------+
| WK40 | WK41 | WK42 | WK43 | WK44 | WK45 |
+-------+--------+--------+--------+--------+-------+
In Table 1, the WK40, WK41 etc. column names are dynamic (i.e., these column names change every week depending on the current week of the year & then shows the last 6 weeks). Table 1 has all the required data.
In Table 2, the column names are W1, W2, ...W6 and the column name here is static. But the data in each column (WK40, WK41....) changes every week based on the current week.
I want to create a table like this:
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
| Category | Name | Goal | W1 | W2 | W3 | W4 | W5 | W6 |
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
| Data | Data | Data | Data | Data | Data | Data | Data | Data |
+---------------+--------+--------+--------+--------+--------+--------+--------+---------+
I've never used PIVOT before so need help creating one for this. Thanks.
sql
sql-server tsql pivot sql
sql-server tsql pivot edited Nov 19 '18 at 8:59
Andrea
7,661144652
asked Nov 14 '18 at 2:36
user10096621user10096621
4016
edited Nov 19 '18 at 8:59
Andrea
7,661144652
asked Nov 14 '18 at 2:36
user10096621user10096621
4016
edited Nov 19 '18 at 8:59
Andrea
7,661144652
edited Nov 19 '18 at 8:59
Andrea
7,661144652
edited Nov 19 '18 at 8:59
Andrea
7,661144652
7,661144652
asked Nov 14 '18 at 2:36
user10096621user10096621
4016
asked Nov 14 '18 at 2:36
user10096621user10096621
4016
asked Nov 14 '18 at 2:36
user10096621user10096621
4016
4016
3
it will help us to understand your situation better if you can add some realistic sample data and the expected result.Dataeverywhere doesn't help much
– Squirrel
Nov 14 '18 at 2:40
1
I would strongly suggest normalising your data rather than finding a quick-fix solution to this particular problem. If you normalise your data, you at least have the option of pivoting whichever way you want after it without having to figure out a silly work-around to ever-changing column names.
– ZLK
Nov 14 '18 at 2:59
I agree with ZLK. Normalization makes work like this so much simpler, and it keeps the solution dynamically able to add new columns on its own. Much easier than having to go back to an old solution to make it able to handle new data needs.
– Utrolig
Nov 14 '18 at 6:24
add a comment |
3
it will help us to understand your situation better if you can add some realistic sample data and the expected result.Dataeverywhere doesn't help much
– Squirrel
Nov 14 '18 at 2:40
1
I would strongly suggest normalising your data rather than finding a quick-fix solution to this particular problem. If you normalise your data, you at least have the option of pivoting whichever way you want after it without having to figure out a silly work-around to ever-changing column names.
– ZLK
Nov 14 '18 at 2:59
I agree with ZLK. Normalization makes work like this so much simpler, and it keeps the solution dynamically able to add new columns on its own. Much easier than having to go back to an old solution to make it able to handle new data needs.
– Utrolig
Nov 14 '18 at 6:24
3
3
it will help us to understand your situation better if you can add some realistic sample data and the expected result.
Data everywhere doesn't help much– Squirrel
Nov 14 '18 at 2:40
it will help us to understand your situation better if you can add some realistic sample data and the expected result.
Data everywhere doesn't help much– Squirrel
Nov 14 '18 at 2:40
1
1
I would strongly suggest normalising your data rather than finding a quick-fix solution to this particular problem. If you normalise your data, you at least have the option of pivoting whichever way you want after it without having to figure out a silly work-around to ever-changing column names.
– ZLK
Nov 14 '18 at 2:59
I would strongly suggest normalising your data rather than finding a quick-fix solution to this particular problem. If you normalise your data, you at least have the option of pivoting whichever way you want after it without having to figure out a silly work-around to ever-changing column names.
– ZLK
Nov 14 '18 at 2:59
I agree with ZLK. Normalization makes work like this so much simpler, and it keeps the solution dynamically able to add new columns on its own. Much easier than having to go back to an old solution to make it able to handle new data needs.
– Utrolig
Nov 14 '18 at 6:24
I agree with ZLK. Normalization makes work like this so much simpler, and it keeps the solution dynamically able to add new columns on its own. Much easier than having to go back to an old solution to make it able to handle new data needs.
– Utrolig
Nov 14 '18 at 6:24
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%2f53292403%2fhow-to-create-a-pivot-using-two-tables%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%2f53292403%2fhow-to-create-a-pivot-using-two-tables%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
3
it will help us to understand your situation better if you can add some realistic sample data and the expected result.
Dataeverywhere doesn't help much– Squirrel
Nov 14 '18 at 2:40
1
I would strongly suggest normalising your data rather than finding a quick-fix solution to this particular problem. If you normalise your data, you at least have the option of pivoting whichever way you want after it without having to figure out a silly work-around to ever-changing column names.
– ZLK
Nov 14 '18 at 2:59
I agree with ZLK. Normalization makes work like this so much simpler, and it keeps the solution dynamically able to add new columns on its own. Much easier than having to go back to an old solution to make it able to handle new data needs.
– Utrolig
Nov 14 '18 at 6:24