How to convert rows to columns using pivot
1
I Have table with data like this
A|B|C|D|E|
----------
100|ABC|OL|Decmber|100
100|ABC|OL|January|200
100|ABC|OL|February|290
100|DEF|OL|Decmber|260
100|DEF|OL|January|300
100|DEF|OL|February|360
200|ABC|OL|December|500
200|ABC|OL|January|600
200|ABC|OL|February|550
200|DEF|OL|December|570
200|DEF|OL|January|600
200|DEF|OL|February|680
----------
I want the output should be
A|B|C|December|January|February
100|ABC|OL|100|290|300
100|DEF|OL|200|260|360
200|ABC|OL|500|550|600
200|DEF|OL|600|570|680
As the values are dynamic so i have below query:-
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
(
SELECT Distinct
','+QUOTENAME(c.[D])
FROM #rolling c FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '');
SET @query = 'SELECT [A],[B],[C],'+@cols+'from (SELECT [A] ,[B],[C],
[D] as [Month],
[E] as [amount]
FROM #rolling
)x pivot (min(amount) for Month in ('+@cols+'))p';
EXECUTE (@query);
But i am getting the output something like this.
A|B|C|ABC|DEF|
100|December|OL|100|200
100|January|OL|290|260
100|February|OL|300|360
200|December|OL|500|600
200|January|OL|550|570
200|February|OL|600|680
As the Month names in that table are dynamic that's why i use this.
But the output i am getting like that.
Please help me how can i achieve the result as i mentioned earlier.
sql
sql-server asked Nov 13 '18 at 8:50
rollroll
43
add a comment |
1
I Have table with data like this
A|B|C|D|E|
----------
100|ABC|OL|Decmber|100
100|ABC|OL|January|200
100|ABC|OL|February|290
100|DEF|OL|Decmber|260
100|DEF|OL|January|300
100|DEF|OL|February|360
200|ABC|OL|December|500
200|ABC|OL|January|600
200|ABC|OL|February|550
200|DEF|OL|December|570
200|DEF|OL|January|600
200|DEF|OL|February|680
----------
I want the output should be
A|B|C|December|January|February
100|ABC|OL|100|290|300
100|DEF|OL|200|260|360
200|ABC|OL|500|550|600
200|DEF|OL|600|570|680
As the values are dynamic so i have below query:-
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
(
SELECT Distinct
','+QUOTENAME(c.[D])
FROM #rolling c FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '');
SET @query = 'SELECT [A],[B],[C],'+@cols+'from (SELECT [A] ,[B],[C],
[D] as [Month],
[E] as [amount]
FROM #rolling
)x pivot (min(amount) for Month in ('+@cols+'))p';
EXECUTE (@query);
But i am getting the output something like this.
A|B|C|ABC|DEF|
100|December|OL|100|200
100|January|OL|290|260
100|February|OL|300|360
200|December|OL|500|600
200|January|OL|550|570
200|February|OL|600|680
As the Month names in that table are dynamic that's why i use this.
But the output i am getting like that.
Please help me how can i achieve the result as i mentioned earlier.
sql
sql-server asked Nov 13 '18 at 8:50
rollroll
43
2
Please don't Spam Tag RDBMS. Edit and add the specific RDBMS you are using.
– Madhur Bhaiya
Nov 13 '18 at 8:51
1
Are you using MySQL, MS SQL Server or Oracle? The answer won't be the same...
– jarlh
Nov 13 '18 at 8:51
add a comment |
1
1
1
0
I Have table with data like this
A|B|C|D|E|
----------
100|ABC|OL|Decmber|100
100|ABC|OL|January|200
100|ABC|OL|February|290
100|DEF|OL|Decmber|260
100|DEF|OL|January|300
100|DEF|OL|February|360
200|ABC|OL|December|500
200|ABC|OL|January|600
200|ABC|OL|February|550
200|DEF|OL|December|570
200|DEF|OL|January|600
200|DEF|OL|February|680
----------
I want the output should be
A|B|C|December|January|February
100|ABC|OL|100|290|300
100|DEF|OL|200|260|360
200|ABC|OL|500|550|600
200|DEF|OL|600|570|680
As the values are dynamic so i have below query:-
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
(
SELECT Distinct
','+QUOTENAME(c.[D])
FROM #rolling c FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '');
SET @query = 'SELECT [A],[B],[C],'+@cols+'from (SELECT [A] ,[B],[C],
[D] as [Month],
[E] as [amount]
FROM #rolling
)x pivot (min(amount) for Month in ('+@cols+'))p';
EXECUTE (@query);
But i am getting the output something like this.
A|B|C|ABC|DEF|
100|December|OL|100|200
100|January|OL|290|260
100|February|OL|300|360
200|December|OL|500|600
200|January|OL|550|570
200|February|OL|600|680
As the Month names in that table are dynamic that's why i use this.
But the output i am getting like that.
Please help me how can i achieve the result as i mentioned earlier.
sql
sql-server asked Nov 13 '18 at 8:50
rollroll
43
I Have table with data like this
A|B|C|D|E|
----------
100|ABC|OL|Decmber|100
100|ABC|OL|January|200
100|ABC|OL|February|290
100|DEF|OL|Decmber|260
100|DEF|OL|January|300
100|DEF|OL|February|360
200|ABC|OL|December|500
200|ABC|OL|January|600
200|ABC|OL|February|550
200|DEF|OL|December|570
200|DEF|OL|January|600
200|DEF|OL|February|680
----------
I want the output should be
A|B|C|December|January|February
100|ABC|OL|100|290|300
100|DEF|OL|200|260|360
200|ABC|OL|500|550|600
200|DEF|OL|600|570|680
As the values are dynamic so i have below query:-
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
(
SELECT Distinct
','+QUOTENAME(c.[D])
FROM #rolling c FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '');
SET @query = 'SELECT [A],[B],[C],'+@cols+'from (SELECT [A] ,[B],[C],
[D] as [Month],
[E] as [amount]
FROM #rolling
)x pivot (min(amount) for Month in ('+@cols+'))p';
EXECUTE (@query);
But i am getting the output something like this.
A|B|C|ABC|DEF|
100|December|OL|100|200
100|January|OL|290|260
100|February|OL|300|360
200|December|OL|500|600
200|January|OL|550|570
200|February|OL|600|680
As the Month names in that table are dynamic that's why i use this.
But the output i am getting like that.
Please help me how can i achieve the result as i mentioned earlier.
sql
sql-server sql
sql-server asked Nov 13 '18 at 8:50
rollroll
43
asked Nov 13 '18 at 8:50
rollroll
43
edited Nov 13 '18 at 9:38
roll
asked Nov 13 '18 at 8:50
rollroll
43
asked Nov 13 '18 at 8:50
rollroll
43
asked Nov 13 '18 at 8:50
rollroll
43
43
2
Please don't Spam Tag RDBMS. Edit and add the specific RDBMS you are using.
– Madhur Bhaiya
Nov 13 '18 at 8:51
1
Are you using MySQL, MS SQL Server or Oracle? The answer won't be the same...
– jarlh
Nov 13 '18 at 8:51
add a comment |
2
Please don't Spam Tag RDBMS. Edit and add the specific RDBMS you are using.
– Madhur Bhaiya
Nov 13 '18 at 8:51
1
Are you using MySQL, MS SQL Server or Oracle? The answer won't be the same...
– jarlh
Nov 13 '18 at 8:51
2
2
Please don't Spam Tag RDBMS. Edit and add the specific RDBMS you are using.
– Madhur Bhaiya
Nov 13 '18 at 8:51
Please don't Spam Tag RDBMS. Edit and add the specific RDBMS you are using.
– Madhur Bhaiya
Nov 13 '18 at 8:51
1
1
Are you using MySQL, MS SQL Server or Oracle? The answer won't be the same...
– jarlh
Nov 13 '18 at 8:51
Are you using MySQL, MS SQL Server or Oracle? The answer won't be the same...
– jarlh
Nov 13 '18 at 8:51
add a comment |
1 Answer
1
active
oldest
votes
0
You can try below - as you use STUFF() function so it seems your dbms is SQL SERVER
DEMO
declare @sql varchar(max)='',@col_list varchar(8000)=''
set @col_list = (select distinct quotename([D])+',' from #rolling
for xml path(''))
set @col_list = left (@col_list,len(@col_list)-1)
set @sql = 'select A,B,C,'+@col_list+' from
#rolling
pivot (max([E]) for [D] in ('+@col_list+'))pv'
exec(@sql)
OUTPUT:
A B C December February January
100 ABC OL 100 290 200
100 DEF OL 260 360 300
200 ABC OL 500 550 600
200 DEF OL 570 680 600
answered Nov 13 '18 at 8:54
fa06fa06
11.7k2917
Thank you so much. Sorry for the wrong table structure i have given the new one now could you please check it once
– roll
Nov 13 '18 at 9:39
@roll where is your table structure
– fa06
Nov 13 '18 at 9:40
The first table i have edited it now
– roll
Nov 13 '18 at 9:42
@roll, I've considered your first table - have you checked the demo
– fa06
Nov 13 '18 at 9:43
Yes,But I am not getting the result as expected.The values are inserted to the data base as i edited now
– roll
Nov 13 '18 at 9:44
|
show 2 more comments
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%2f53277077%2fhow-to-convert-rows-to-columns-using-pivot%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
0
You can try below - as you use STUFF() function so it seems your dbms is SQL SERVER
DEMO
declare @sql varchar(max)='',@col_list varchar(8000)=''
set @col_list = (select distinct quotename([D])+',' from #rolling
for xml path(''))
set @col_list = left (@col_list,len(@col_list)-1)
set @sql = 'select A,B,C,'+@col_list+' from
#rolling
pivot (max([E]) for [D] in ('+@col_list+'))pv'
exec(@sql)
OUTPUT:
A B C December February January
100 ABC OL 100 290 200
100 DEF OL 260 360 300
200 ABC OL 500 550 600
200 DEF OL 570 680 600
answered Nov 13 '18 at 8:54
fa06fa06
11.7k2917
Thank you so much. Sorry for the wrong table structure i have given the new one now could you please check it once
– roll
Nov 13 '18 at 9:39
@roll where is your table structure
– fa06
Nov 13 '18 at 9:40
The first table i have edited it now
– roll
Nov 13 '18 at 9:42
@roll, I've considered your first table - have you checked the demo
– fa06
Nov 13 '18 at 9:43
Yes,But I am not getting the result as expected.The values are inserted to the data base as i edited now
– roll
Nov 13 '18 at 9:44
|
show 2 more comments
0
You can try below - as you use STUFF() function so it seems your dbms is SQL SERVER
DEMO
declare @sql varchar(max)='',@col_list varchar(8000)=''
set @col_list = (select distinct quotename([D])+',' from #rolling
for xml path(''))
set @col_list = left (@col_list,len(@col_list)-1)
set @sql = 'select A,B,C,'+@col_list+' from
#rolling
pivot (max([E]) for [D] in ('+@col_list+'))pv'
exec(@sql)
OUTPUT:
A B C December February January
100 ABC OL 100 290 200
100 DEF OL 260 360 300
200 ABC OL 500 550 600
200 DEF OL 570 680 600
answered Nov 13 '18 at 8:54
fa06fa06
11.7k2917
Thank you so much. Sorry for the wrong table structure i have given the new one now could you please check it once
– roll
Nov 13 '18 at 9:39
@roll where is your table structure
– fa06
Nov 13 '18 at 9:40
The first table i have edited it now
– roll
Nov 13 '18 at 9:42
@roll, I've considered your first table - have you checked the demo
– fa06
Nov 13 '18 at 9:43
Yes,But I am not getting the result as expected.The values are inserted to the data base as i edited now
– roll
Nov 13 '18 at 9:44
|
show 2 more comments
0
0
0
You can try below - as you use STUFF() function so it seems your dbms is SQL SERVER
DEMO
declare @sql varchar(max)='',@col_list varchar(8000)=''
set @col_list = (select distinct quotename([D])+',' from #rolling
for xml path(''))
set @col_list = left (@col_list,len(@col_list)-1)
set @sql = 'select A,B,C,'+@col_list+' from
#rolling
pivot (max([E]) for [D] in ('+@col_list+'))pv'
exec(@sql)
OUTPUT:
A B C December February January
100 ABC OL 100 290 200
100 DEF OL 260 360 300
200 ABC OL 500 550 600
200 DEF OL 570 680 600
answered Nov 13 '18 at 8:54
fa06fa06
11.7k2917
You can try below - as you use STUFF() function so it seems your dbms is SQL SERVER
DEMO
declare @sql varchar(max)='',@col_list varchar(8000)=''
set @col_list = (select distinct quotename([D])+',' from #rolling
for xml path(''))
set @col_list = left (@col_list,len(@col_list)-1)
set @sql = 'select A,B,C,'+@col_list+' from
#rolling
pivot (max([E]) for [D] in ('+@col_list+'))pv'
exec(@sql)
OUTPUT:
A B C December February January
100 ABC OL 100 290 200
100 DEF OL 260 360 300
200 ABC OL 500 550 600
200 DEF OL 570 680 600
answered Nov 13 '18 at 8:54
fa06fa06
11.7k2917
edited Nov 13 '18 at 10:02
answered Nov 13 '18 at 8:54
fa06fa06
11.7k2917
answered Nov 13 '18 at 8:54
fa06fa06
11.7k2917
answered Nov 13 '18 at 8:54
fa06fa06
11.7k2917
11.7k2917
Thank you so much. Sorry for the wrong table structure i have given the new one now could you please check it once
– roll
Nov 13 '18 at 9:39
@roll where is your table structure
– fa06
Nov 13 '18 at 9:40
The first table i have edited it now
– roll
Nov 13 '18 at 9:42
@roll, I've considered your first table - have you checked the demo
– fa06
Nov 13 '18 at 9:43
Yes,But I am not getting the result as expected.The values are inserted to the data base as i edited now
– roll
Nov 13 '18 at 9:44
|
show 2 more comments
Thank you so much. Sorry for the wrong table structure i have given the new one now could you please check it once
– roll
Nov 13 '18 at 9:39
@roll where is your table structure
– fa06
Nov 13 '18 at 9:40
The first table i have edited it now
– roll
Nov 13 '18 at 9:42
@roll, I've considered your first table - have you checked the demo
– fa06
Nov 13 '18 at 9:43
Yes,But I am not getting the result as expected.The values are inserted to the data base as i edited now
– roll
Nov 13 '18 at 9:44
Thank you so much. Sorry for the wrong table structure i have given the new one now could you please check it once
– roll
Nov 13 '18 at 9:39
Thank you so much. Sorry for the wrong table structure i have given the new one now could you please check it once
– roll
Nov 13 '18 at 9:39
@roll where is your table structure
– fa06
Nov 13 '18 at 9:40
@roll where is your table structure
– fa06
Nov 13 '18 at 9:40
The first table i have edited it now
– roll
Nov 13 '18 at 9:42
The first table i have edited it now
– roll
Nov 13 '18 at 9:42
@roll, I've considered your first table - have you checked the demo
– fa06
Nov 13 '18 at 9:43
@roll, I've considered your first table - have you checked the demo
– fa06
Nov 13 '18 at 9:43
Yes,But I am not getting the result as expected.The values are inserted to the data base as i edited now
– roll
Nov 13 '18 at 9:44
Yes,But I am not getting the result as expected.The values are inserted to the data base as i edited now
– roll
Nov 13 '18 at 9:44
|
show 2 more comments
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%2f53277077%2fhow-to-convert-rows-to-columns-using-pivot%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
2
Please don't Spam Tag RDBMS. Edit and add the specific RDBMS you are using.
– Madhur Bhaiya
Nov 13 '18 at 8:51
1
Are you using MySQL, MS SQL Server or Oracle? The answer won't be the same...
– jarlh
Nov 13 '18 at 8:51