Pivot Rows to Column By Group in SQL Server

I am looking for a code which can transpose row to column using the group by in the SQL Server 2008.

This is my table :

Name | Stdy | Val
-------+---------+-------
Kunjan | Technic | 80
Kunjan | Sains | 90
Kunjan | Sport | 60
Shone | Technic | 60
Shone | Sains | 80
Shone | Sport | 70
Peudd | Technic | 85
Peudd | Sains | 75
Peudd | Sport | 90

What I want to eventually display is something like this (for the data above):

Stdy | Kunjan | Shone | Peudd
--------+--------+-------+-------
Technic | 80 | 60 | 85
Sains | 90 | 80 | 75
Sport | 60 | 70 | 90

Any help would be appreciated.

Thanks in advance.

edited Nov 15 '18 at 5:18

marc_s

583k13011241270

asked Nov 15 '18 at 1:10

dayat

104

mssqltips.com/sqlservertip/2783/…

– Aaron Bertrand
Nov 15 '18 at 2:35

add a comment |

I am looking for a code which can transpose row to column using the group by in the SQL Server 2008.

This is my table :

Name | Stdy | Val
-------+---------+-------
Kunjan | Technic | 80
Kunjan | Sains | 90
Kunjan | Sport | 60
Shone | Technic | 60
Shone | Sains | 80
Shone | Sport | 70
Peudd | Technic | 85
Peudd | Sains | 75
Peudd | Sport | 90

What I want to eventually display is something like this (for the data above):

Stdy | Kunjan | Shone | Peudd
--------+--------+-------+-------
Technic | 80 | 60 | 85
Sains | 90 | 80 | 75
Sport | 60 | 70 | 90

Any help would be appreciated.

Thanks in advance.

edited Nov 15 '18 at 5:18

marc_s

583k13011241270

asked Nov 15 '18 at 1:10

dayat

104

mssqltips.com/sqlservertip/2783/…

– Aaron Bertrand
Nov 15 '18 at 2:35

add a comment |

I am looking for a code which can transpose row to column using the group by in the SQL Server 2008.

This is my table :

Name | Stdy | Val
-------+---------+-------
Kunjan | Technic | 80
Kunjan | Sains | 90
Kunjan | Sport | 60
Shone | Technic | 60
Shone | Sains | 80
Shone | Sport | 70
Peudd | Technic | 85
Peudd | Sains | 75
Peudd | Sport | 90

What I want to eventually display is something like this (for the data above):

Stdy | Kunjan | Shone | Peudd
--------+--------+-------+-------
Technic | 80 | 60 | 85
Sains | 90 | 80 | 75
Sport | 60 | 70 | 90

Any help would be appreciated.

Thanks in advance.

edited Nov 15 '18 at 5:18

marc_s

583k13011241270

asked Nov 15 '18 at 1:10

dayat

104

I am looking for a code which can transpose row to column using the group by in the SQL Server 2008.

This is my table :

Name | Stdy | Val
-------+---------+-------
Kunjan | Technic | 80
Kunjan | Sains | 90
Kunjan | Sport | 60
Shone | Technic | 60
Shone | Sains | 80
Shone | Sport | 70
Peudd | Technic | 85
Peudd | Sains | 75
Peudd | Sport | 90

What I want to eventually display is something like this (for the data above):

Stdy | Kunjan | Shone | Peudd
--------+--------+-------+-------
Technic | 80 | 60 | 85
Sains | 90 | 80 | 75
Sport | 60 | 70 | 90

Any help would be appreciated.

Thanks in advance.

sql-server sql-server-2008 pivot

edited Nov 15 '18 at 5:18

marc_s

583k13011241270

asked Nov 15 '18 at 1:10

dayat

104

edited Nov 15 '18 at 5:18

marc_s

583k13011241270

asked Nov 15 '18 at 1:10

dayat

104

edited Nov 15 '18 at 5:18

marc_s

583k13011241270

edited Nov 15 '18 at 5:18

marc_s

583k13011241270

edited Nov 15 '18 at 5:18

marc_s

583k13011241270

asked Nov 15 '18 at 1:10

dayat

104

asked Nov 15 '18 at 1:10

dayat

104

asked Nov 15 '18 at 1:10

dayat

104

mssqltips.com/sqlservertip/2783/…

– Aaron Bertrand
Nov 15 '18 at 2:35

add a comment |

mssqltips.com/sqlservertip/2783/…

– Aaron Bertrand
Nov 15 '18 at 2:35

mssqltips.com/sqlservertip/2783/…

– Aaron Bertrand
Nov 15 '18 at 2:35

add a comment |

1 Answer
1

active

oldest

votes

this can be done using PIVOT operator

select *
from yourtable
 pivot 
 (
 max(Val)
 for Name in ([Kunjan], [Shone], [Peudd])
 ) p

or conditional CASE statement. There are lots of example, just do a search on it

EDIT : for dynamic case

declare @sql nvarchar(max),
 @col nvarchar(max)

select @col = isnull(@col + ',', '') + Name
from yourtable
group by Name

print @col 

select @sql = 
'
select *
from youtable
 pivot 
 (
 max(Val)
 for Name in (' + @col + ')
 ) p
'

print @sql
exec sp_executesql @sql

edited Nov 15 '18 at 2:02

answered Nov 15 '18 at 1:34

Squirrel

12.1k22228

but then how do I make the name flexible, because the Count of names or the name is not always the same

– dayat
Nov 15 '18 at 1:49

1

then you will required Dynamic SQL. Give me few mins, I will update my answer

– Squirrel
Nov 15 '18 at 1:55

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

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53311054%2fpivot-rows-to-column-by-group-in-sql-server%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

this can be done using PIVOT operator

select *
from yourtable
 pivot 
 (
 max(Val)
 for Name in ([Kunjan], [Shone], [Peudd])
 ) p

or conditional CASE statement. There are lots of example, just do a search on it

EDIT : for dynamic case

declare @sql nvarchar(max),
 @col nvarchar(max)

select @col = isnull(@col + ',', '') + Name
from yourtable
group by Name

print @col 

select @sql = 
'
select *
from youtable
 pivot 
 (
 max(Val)
 for Name in (' + @col + ')
 ) p
'

print @sql
exec sp_executesql @sql

edited Nov 15 '18 at 2:02

answered Nov 15 '18 at 1:34

Squirrel

12.1k22228

but then how do I make the name flexible, because the Count of names or the name is not always the same

– dayat
Nov 15 '18 at 1:49

1

then you will required Dynamic SQL. Give me few mins, I will update my answer

– Squirrel
Nov 15 '18 at 1:55

add a comment |

this can be done using PIVOT operator

select *
from yourtable
 pivot 
 (
 max(Val)
 for Name in ([Kunjan], [Shone], [Peudd])
 ) p

or conditional CASE statement. There are lots of example, just do a search on it

EDIT : for dynamic case

declare @sql nvarchar(max),
 @col nvarchar(max)

select @col = isnull(@col + ',', '') + Name
from yourtable
group by Name

print @col 

select @sql = 
'
select *
from youtable
 pivot 
 (
 max(Val)
 for Name in (' + @col + ')
 ) p
'

print @sql
exec sp_executesql @sql

edited Nov 15 '18 at 2:02

answered Nov 15 '18 at 1:34

Squirrel

12.1k22228

but then how do I make the name flexible, because the Count of names or the name is not always the same

– dayat
Nov 15 '18 at 1:49

1

then you will required Dynamic SQL. Give me few mins, I will update my answer

– Squirrel
Nov 15 '18 at 1:55

add a comment |

this can be done using PIVOT operator

select *
from yourtable
 pivot 
 (
 max(Val)
 for Name in ([Kunjan], [Shone], [Peudd])
 ) p

or conditional CASE statement. There are lots of example, just do a search on it

EDIT : for dynamic case

declare @sql nvarchar(max),
 @col nvarchar(max)

select @col = isnull(@col + ',', '') + Name
from yourtable
group by Name

print @col 

select @sql = 
'
select *
from youtable
 pivot 
 (
 max(Val)
 for Name in (' + @col + ')
 ) p
'

print @sql
exec sp_executesql @sql

edited Nov 15 '18 at 2:02

answered Nov 15 '18 at 1:34

Squirrel

12.1k22228

this can be done using PIVOT operator

select *
from yourtable
 pivot 
 (
 max(Val)
 for Name in ([Kunjan], [Shone], [Peudd])
 ) p

or conditional CASE statement. There are lots of example, just do a search on it

EDIT : for dynamic case

declare @sql nvarchar(max),
 @col nvarchar(max)

select @col = isnull(@col + ',', '') + Name
from yourtable
group by Name

print @col 

select @sql = 
'
select *
from youtable
 pivot 
 (
 max(Val)
 for Name in (' + @col + ')
 ) p
'

print @sql
exec sp_executesql @sql

edited Nov 15 '18 at 2:02

answered Nov 15 '18 at 1:34

Squirrel

12.1k22228

edited Nov 15 '18 at 2:02

answered Nov 15 '18 at 1:34

Squirrel

12.1k22228

answered Nov 15 '18 at 1:34

Squirrel

12.1k22228

answered Nov 15 '18 at 1:34

Squirrel

12.1k22228

but then how do I make the name flexible, because the Count of names or the name is not always the same

– dayat
Nov 15 '18 at 1:49

1

then you will required Dynamic SQL. Give me few mins, I will update my answer

– Squirrel
Nov 15 '18 at 1:55

add a comment |

but then how do I make the name flexible, because the Count of names or the name is not always the same

– dayat
Nov 15 '18 at 1:49

1

then you will required Dynamic SQL. Give me few mins, I will update my answer

– Squirrel
Nov 15 '18 at 1:55

but then how do I make the name flexible, because the Count of names or the name is not always the same

– dayat
Nov 15 '18 at 1:49

then you will required Dynamic SQL. Give me few mins, I will update my answer

– Squirrel
Nov 15 '18 at 1:55

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

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Pfthb