频道导航

SQLServer行转列实现思路记录

2019-01-04 MsSQL 前端之家

前端之家收集整理的这篇文章主要介绍了SQLServer行转列实现思路记录，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

最近面试遇到了一道面试题，顿时有点迷糊，只说出了思路，后来百度了一下，整理了一下思路，于是记录下来，方便以后学习。（面试题请参见附件）相关的数据表： 1.score表
<IMG src="https:https://files.jb51.cc/file_images/article/201406/20140617093835.jpeg?20145179 404">
2.[User]表
<IMG src="https:https://files.jb51.cc/file_images/article/201406/20140617094025.jpeg?20145179 4045">
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

行转列