SQLServer行转列实现思路记录

最近面试遇到了一道面试题，顿时有点迷糊，只说出了思路，后来百度了一下，整理了一下思路，于是记录下来，方便以后学习。（面试题请参见附件） 相关的数据表： 1.Score表
<IMG src="https:https://files.52php.cn/file_images/article/201406/20140617093835.jpeg?20145179404"&gt;
2.[User]表
<IMG src="https:https://files.52php.cn/file_images/article/201406/20140617094025.jpeg?201451794045"&gt;
SQL语句如下： --方法一：静态SQL
<div class="codetitle"><a style="CURSOR: pointer" data="91090" class="copybut" id="copybut91090" onclick="doCopy('code91090')"> 代码如下:<div class="codebody" id="code91090">
SELECT FROM
(SELECT UID,Name,Score,ScoreName FROM Score,[User] WHERE Score.UID=[User].ID) AS SourceTable
PIVOT(AVG(Score)FOR ScoreName IN ([英语],[数学])) AS a

--方法二：动态SQL
<div class="codetitle"><a style="CURSOR: pointer" data="82804" class="copybut" id="copybut82804" onclick="doCopy('code82804')"> 代码如下:<div class="codebody" id="code82804">
DECLARE @s NVARCHAR(4000)
SELECT @s = ISNULL(@s + ',','') + QUOTENAME(ScoreName)
FROM (select distinct ScoreName from Score) as A ---列名不要重复 Declare @sql NVARCHAR(4000)
SET @sql='
select r. from
(select UID,ScoreName,Score from Score,[User] where Score.UID=[User].ID) as t
pivot
(
max(t.Score)
for t.ScoreName in ('+@s+')
) as r'
EXEC( @sql)

--方法三：Case When
<div class="codetitle"><a style="CURSOR: pointer" data="60777" class="copybut" id="copybut60777" onclick="doCopy('code60777')"> 代码如下:<div class="codebody" id="code60777">
select
row_number() OVER(ORDER BY [User].ID) as 编号,
UID as 用户编号,
Name as 姓名,
max(case ScoreName when '英语' then Score else 0 end) 英语,
max(case ScoreName when '数学' then Score else 0 end) 数学
from Score,[User] WHERE Score.UID=[User].ID
group by UID,[User].ID,Name

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!