Mixing
I want to pivot table
3
i want TO PIVOT DATA AS per given below format,
in below i put a code for create table with data which i have already.
CREATE TABLE #temp
(
SHOW VARCHAR(100),
moviename VARCHAR(100),
ticketrate INT,
collectiondate DATETIME,
totalcaroccupancy DECIMAL(18,2)
)
INSERT INTO #temp
(
SHOW,
moviename,
ticketrate,
collectiondate,
totalcaroccupancy
)
SELECT 'Evening','DASSEHRA',70,'2018-10-26 00:00:00.000','11440.00' union all
SELECT 'Evening','DASSEHRA',70,'2018-10-27 00:00:00.000','16016.00' union all
SELECT 'Night','Test Thugs',70,'2018-10-26 00:00:00.000','14560.00' union all
SELECT 'Night','Test Thugs',70,'2018-10-27 00:00:00.000','15600.00'
my current table data as below :
SHOW moviename ticketrate collectiondate totalcaroccupancy
Evening DASSEHRA 70 2018-10-26 00:00:00.000 11440.00
Evening DASSEHRA 70 2018-10-27 00:00:00.000 16016.00
Night Test Thugs 70 2018-10-26 00:00:00.000 14560.00
Night Test Thugs 70 2018-10-27 00:00:00.000 15600.00
i want output as below :
collectiondate eveningticketrate eveningticketrate eveningMovieName nightMovieName eveningtotalcaroccupancy nighttotalcaroccupancy
2018-10-26 00:00:00.000 70 70 DASSEHRA Test Thugs 11440.00 14560.00
2018-10-27 00:00:00.000 70 70 DASSEHRA Test Thugs 16016.00 15600.00
sql
sql-server sql-server-2012 asked Nov 20 '18 at 6:49
shaelshael
886
add a comment |
3
i want TO PIVOT DATA AS per given below format,
in below i put a code for create table with data which i have already.
CREATE TABLE #temp
(
SHOW VARCHAR(100),
moviename VARCHAR(100),
ticketrate INT,
collectiondate DATETIME,
totalcaroccupancy DECIMAL(18,2)
)
INSERT INTO #temp
(
SHOW,
moviename,
ticketrate,
collectiondate,
totalcaroccupancy
)
SELECT 'Evening','DASSEHRA',70,'2018-10-26 00:00:00.000','11440.00' union all
SELECT 'Evening','DASSEHRA',70,'2018-10-27 00:00:00.000','16016.00' union all
SELECT 'Night','Test Thugs',70,'2018-10-26 00:00:00.000','14560.00' union all
SELECT 'Night','Test Thugs',70,'2018-10-27 00:00:00.000','15600.00'
my current table data as below :
SHOW moviename ticketrate collectiondate totalcaroccupancy
Evening DASSEHRA 70 2018-10-26 00:00:00.000 11440.00
Evening DASSEHRA 70 2018-10-27 00:00:00.000 16016.00
Night Test Thugs 70 2018-10-26 00:00:00.000 14560.00
Night Test Thugs 70 2018-10-27 00:00:00.000 15600.00
i want output as below :
collectiondate eveningticketrate eveningticketrate eveningMovieName nightMovieName eveningtotalcaroccupancy nighttotalcaroccupancy
2018-10-26 00:00:00.000 70 70 DASSEHRA Test Thugs 11440.00 14560.00
2018-10-27 00:00:00.000 70 70 DASSEHRA Test Thugs 16016.00 15600.00
sql
sql-server sql-server-2012 asked Nov 20 '18 at 6:49
shaelshael
886
add a comment |
3
3
3
i want TO PIVOT DATA AS per given below format,
in below i put a code for create table with data which i have already.
CREATE TABLE #temp
(
SHOW VARCHAR(100),
moviename VARCHAR(100),
ticketrate INT,
collectiondate DATETIME,
totalcaroccupancy DECIMAL(18,2)
)
INSERT INTO #temp
(
SHOW,
moviename,
ticketrate,
collectiondate,
totalcaroccupancy
)
SELECT 'Evening','DASSEHRA',70,'2018-10-26 00:00:00.000','11440.00' union all
SELECT 'Evening','DASSEHRA',70,'2018-10-27 00:00:00.000','16016.00' union all
SELECT 'Night','Test Thugs',70,'2018-10-26 00:00:00.000','14560.00' union all
SELECT 'Night','Test Thugs',70,'2018-10-27 00:00:00.000','15600.00'
my current table data as below :
SHOW moviename ticketrate collectiondate totalcaroccupancy
Evening DASSEHRA 70 2018-10-26 00:00:00.000 11440.00
Evening DASSEHRA 70 2018-10-27 00:00:00.000 16016.00
Night Test Thugs 70 2018-10-26 00:00:00.000 14560.00
Night Test Thugs 70 2018-10-27 00:00:00.000 15600.00
i want output as below :
collectiondate eveningticketrate eveningticketrate eveningMovieName nightMovieName eveningtotalcaroccupancy nighttotalcaroccupancy
2018-10-26 00:00:00.000 70 70 DASSEHRA Test Thugs 11440.00 14560.00
2018-10-27 00:00:00.000 70 70 DASSEHRA Test Thugs 16016.00 15600.00
sql
sql-server sql-server-2012 asked Nov 20 '18 at 6:49
shaelshael
886
i want TO PIVOT DATA AS per given below format,
in below i put a code for create table with data which i have already.
CREATE TABLE #temp
(
SHOW VARCHAR(100),
moviename VARCHAR(100),
ticketrate INT,
collectiondate DATETIME,
totalcaroccupancy DECIMAL(18,2)
)
INSERT INTO #temp
(
SHOW,
moviename,
ticketrate,
collectiondate,
totalcaroccupancy
)
SELECT 'Evening','DASSEHRA',70,'2018-10-26 00:00:00.000','11440.00' union all
SELECT 'Evening','DASSEHRA',70,'2018-10-27 00:00:00.000','16016.00' union all
SELECT 'Night','Test Thugs',70,'2018-10-26 00:00:00.000','14560.00' union all
SELECT 'Night','Test Thugs',70,'2018-10-27 00:00:00.000','15600.00'
my current table data as below :
SHOW moviename ticketrate collectiondate totalcaroccupancy
Evening DASSEHRA 70 2018-10-26 00:00:00.000 11440.00
Evening DASSEHRA 70 2018-10-27 00:00:00.000 16016.00
Night Test Thugs 70 2018-10-26 00:00:00.000 14560.00
Night Test Thugs 70 2018-10-27 00:00:00.000 15600.00
i want output as below :
collectiondate eveningticketrate eveningticketrate eveningMovieName nightMovieName eveningtotalcaroccupancy nighttotalcaroccupancy
2018-10-26 00:00:00.000 70 70 DASSEHRA Test Thugs 11440.00 14560.00
2018-10-27 00:00:00.000 70 70 DASSEHRA Test Thugs 16016.00 15600.00
sql
sql-server sql-server-2012 sql
sql-server sql-server-2012 asked Nov 20 '18 at 6:49
shaelshael
886
asked Nov 20 '18 at 6:49
shaelshael
886
asked Nov 20 '18 at 6:49
shaelshael
886
asked Nov 20 '18 at 6:49
shaelshael
886
asked Nov 20 '18 at 6:49
shaelshael
886
886
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
4
simply use conditional case statement as shown below
select collectiondate,
max(case when SHOW = 'Evening' then ticketrate end) as eveningticketrate,
max(case when SHOW = 'Night' then ticketrate end) as nightticketrate,
max(case when SHOW = 'Evening' then moviename end) as eveningmoviename,
max(case when SHOW = 'Night' then moviename end) as nightmoviename,
max(case when SHOW = 'Evening' then totalcaroccupancy end) as eveningtotalcaroccupancy,
max(case when SHOW = 'Night' then totalcaroccupancy end) as nighttotalcaroccupancy
from #temp
group by collectiondate
answered Nov 20 '18 at 7:01
SquirrelSquirrel
11.8k22027
add a comment |
0
An alternative is to create 2 subqueries, one for evening, the other for night and join them together
SELECT evening.collectiondate, eveningticketrate, eveningmoviename, eveningtotalcaroccupancy,
nightticketrate, nightmoviename, nighttotalcaroccupancy
FROM (
SELECT collectiondate, ticketrate as eveningticketrate, moviename as eveningmoviename, totalcaroccupancy as eveningtotalcaroccupancy
FROM #temp
WHERE show = 'Evening') evening
INNER JOIN (
SELECT collectiondate, ticketrate as nightticketrate, moviename as nightmoviename, totalcaroccupancy as nighttotalcaroccupancy
FROM #temp
WHERE show = 'Night') night ON evening.collectiondate = night.collectiondate
ORDER BY evening.collectiondate;
answered Nov 20 '18 at 9:47
PhilSPhilS
42925
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%2f53387647%2fi-want-to-pivot-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
4
simply use conditional case statement as shown below
select collectiondate,
max(case when SHOW = 'Evening' then ticketrate end) as eveningticketrate,
max(case when SHOW = 'Night' then ticketrate end) as nightticketrate,
max(case when SHOW = 'Evening' then moviename end) as eveningmoviename,
max(case when SHOW = 'Night' then moviename end) as nightmoviename,
max(case when SHOW = 'Evening' then totalcaroccupancy end) as eveningtotalcaroccupancy,
max(case when SHOW = 'Night' then totalcaroccupancy end) as nighttotalcaroccupancy
from #temp
group by collectiondate
answered Nov 20 '18 at 7:01
SquirrelSquirrel
11.8k22027
add a comment |
4
simply use conditional case statement as shown below
select collectiondate,
max(case when SHOW = 'Evening' then ticketrate end) as eveningticketrate,
max(case when SHOW = 'Night' then ticketrate end) as nightticketrate,
max(case when SHOW = 'Evening' then moviename end) as eveningmoviename,
max(case when SHOW = 'Night' then moviename end) as nightmoviename,
max(case when SHOW = 'Evening' then totalcaroccupancy end) as eveningtotalcaroccupancy,
max(case when SHOW = 'Night' then totalcaroccupancy end) as nighttotalcaroccupancy
from #temp
group by collectiondate
answered Nov 20 '18 at 7:01
SquirrelSquirrel
11.8k22027
add a comment |
4
4
4
simply use conditional case statement as shown below
select collectiondate,
max(case when SHOW = 'Evening' then ticketrate end) as eveningticketrate,
max(case when SHOW = 'Night' then ticketrate end) as nightticketrate,
max(case when SHOW = 'Evening' then moviename end) as eveningmoviename,
max(case when SHOW = 'Night' then moviename end) as nightmoviename,
max(case when SHOW = 'Evening' then totalcaroccupancy end) as eveningtotalcaroccupancy,
max(case when SHOW = 'Night' then totalcaroccupancy end) as nighttotalcaroccupancy
from #temp
group by collectiondate
answered Nov 20 '18 at 7:01
SquirrelSquirrel
11.8k22027
simply use conditional case statement as shown below
select collectiondate,
max(case when SHOW = 'Evening' then ticketrate end) as eveningticketrate,
max(case when SHOW = 'Night' then ticketrate end) as nightticketrate,
max(case when SHOW = 'Evening' then moviename end) as eveningmoviename,
max(case when SHOW = 'Night' then moviename end) as nightmoviename,
max(case when SHOW = 'Evening' then totalcaroccupancy end) as eveningtotalcaroccupancy,
max(case when SHOW = 'Night' then totalcaroccupancy end) as nighttotalcaroccupancy
from #temp
group by collectiondate
answered Nov 20 '18 at 7:01
SquirrelSquirrel
11.8k22027
answered Nov 20 '18 at 7:01
SquirrelSquirrel
11.8k22027
answered Nov 20 '18 at 7:01
SquirrelSquirrel
11.8k22027
answered Nov 20 '18 at 7:01
SquirrelSquirrel
11.8k22027
11.8k22027
add a comment |
add a comment |
0
An alternative is to create 2 subqueries, one for evening, the other for night and join them together
SELECT evening.collectiondate, eveningticketrate, eveningmoviename, eveningtotalcaroccupancy,
nightticketrate, nightmoviename, nighttotalcaroccupancy
FROM (
SELECT collectiondate, ticketrate as eveningticketrate, moviename as eveningmoviename, totalcaroccupancy as eveningtotalcaroccupancy
FROM #temp
WHERE show = 'Evening') evening
INNER JOIN (
SELECT collectiondate, ticketrate as nightticketrate, moviename as nightmoviename, totalcaroccupancy as nighttotalcaroccupancy
FROM #temp
WHERE show = 'Night') night ON evening.collectiondate = night.collectiondate
ORDER BY evening.collectiondate;
answered Nov 20 '18 at 9:47
PhilSPhilS
42925
add a comment |
0
An alternative is to create 2 subqueries, one for evening, the other for night and join them together
SELECT evening.collectiondate, eveningticketrate, eveningmoviename, eveningtotalcaroccupancy,
nightticketrate, nightmoviename, nighttotalcaroccupancy
FROM (
SELECT collectiondate, ticketrate as eveningticketrate, moviename as eveningmoviename, totalcaroccupancy as eveningtotalcaroccupancy
FROM #temp
WHERE show = 'Evening') evening
INNER JOIN (
SELECT collectiondate, ticketrate as nightticketrate, moviename as nightmoviename, totalcaroccupancy as nighttotalcaroccupancy
FROM #temp
WHERE show = 'Night') night ON evening.collectiondate = night.collectiondate
ORDER BY evening.collectiondate;
answered Nov 20 '18 at 9:47
PhilSPhilS
42925
add a comment |
0
0
0
An alternative is to create 2 subqueries, one for evening, the other for night and join them together
SELECT evening.collectiondate, eveningticketrate, eveningmoviename, eveningtotalcaroccupancy,
nightticketrate, nightmoviename, nighttotalcaroccupancy
FROM (
SELECT collectiondate, ticketrate as eveningticketrate, moviename as eveningmoviename, totalcaroccupancy as eveningtotalcaroccupancy
FROM #temp
WHERE show = 'Evening') evening
INNER JOIN (
SELECT collectiondate, ticketrate as nightticketrate, moviename as nightmoviename, totalcaroccupancy as nighttotalcaroccupancy
FROM #temp
WHERE show = 'Night') night ON evening.collectiondate = night.collectiondate
ORDER BY evening.collectiondate;
answered Nov 20 '18 at 9:47
PhilSPhilS
42925
An alternative is to create 2 subqueries, one for evening, the other for night and join them together
SELECT evening.collectiondate, eveningticketrate, eveningmoviename, eveningtotalcaroccupancy,
nightticketrate, nightmoviename, nighttotalcaroccupancy
FROM (
SELECT collectiondate, ticketrate as eveningticketrate, moviename as eveningmoviename, totalcaroccupancy as eveningtotalcaroccupancy
FROM #temp
WHERE show = 'Evening') evening
INNER JOIN (
SELECT collectiondate, ticketrate as nightticketrate, moviename as nightmoviename, totalcaroccupancy as nighttotalcaroccupancy
FROM #temp
WHERE show = 'Night') night ON evening.collectiondate = night.collectiondate
ORDER BY evening.collectiondate;
answered Nov 20 '18 at 9:47
PhilSPhilS
42925
answered Nov 20 '18 at 9:47
PhilSPhilS
42925
answered Nov 20 '18 at 9:47
PhilSPhilS
42925
answered Nov 20 '18 at 9:47
PhilSPhilS
42925
42925
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.
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%2f53387647%2fi-want-to-pivot-table%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
