我该如何更改这个表格
Name subject Mark
Aswin physics 100
Aswin chemistry 300
Aswin maths 200
介绍
Aswin Physics 100 Chemistry 300 Maths 200
Any one please help me.
我该如何更改这个表格
Name subject Mark
Aswin physics 100
Aswin chemistry 300
Aswin maths 200
介绍
Aswin Physics 100 Chemistry 300 Maths 200
Any one please help me.
select Name,
sum(CASE
when [subject]='physics' then Mark
end) as Physics,
sum(CASE
when [subject]='chemistry' then Mark
end) as chemistry,
sum(CASE
when [subject]='maths' then Mark
end) as maths
from t group by Name
或者如果您需要单行版本:
SELECT
t1.name,
MemberList = substring((SELECT ( ', ' + subject+' - '+
cast(Mark as varchar(100)) )
FROM t t2
WHERE t1.name = t2.name
ORDER BY
name,
subject
FOR XML PATH( '' )
), 3, 1000 )FROM t t1
GROUP BY name
代码示例
虽然我还没有用实际数据测试过这个代码,但它可以正常解析。
-- Pivot Table ordered by Name of Student
SELECT Name, Physics, Chemistry, Maths
FROM (
SELECT Name, Subject, Mark
FROM Student) up
PIVOT (SUM(Mark) FOR Student IN (Physics, Chemistry, Maths)) AS pvt
ORDER BY Name
-- Result should be something like
----------------------------------
Name Physics Chemistry Maths
----------------------------------
Aswin 100 300 200
----------------------------------
目前还不确定您是想把这些数据分别列在不同的列中还是在同一列中。
如果您想要分别列在不同的列中,那么您可以使用SQL Server 2005中提供的PIVOT
函数。
如果您已经知道了要转换的所有值或者数量有限,则可以硬编码查询语句:
select *
from
(
select name, subject +' '+ cast(mark as varchar(9)) as sub_mark,
'Subject_'+cast(row_number() over(partition by name
order by subject) as varchar(10)) col_name
from subjects
) s
pivot
(
max(sub_mark)
for col_name in (Subject_1, Subject_2, Subject_3)
) piv;
Subject_1
等。DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Subject_'+cast(row_number() over(partition by name
order by subject) as varchar(10)))
from subjects
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT name,' + @cols + ' from
(
select name, subject +'' ''+ cast(mark as varchar(9)) as sub_mark,
''Subject_''+cast(row_number() over(partition by name
order by subject) as varchar(10)) col_name
from subjects
) x
pivot
(
max(sub_mark)
for col_name in (' + @cols + ')
) p '
execute(@query)
name
有超过3个科目,则动态SQL版本将增加列数。| NAME | SUBJECT_1 | SUBJECT_2 | SUBJECT_3 |
---------------------------------------------------
| Aswin | chemistry 300 | maths 200 | physics 100 |