菜单

一、行专列

1.1、数据准备：添加数据到数据库

select a.* into #XinTest from (
select 'Fox' Name,'男' Sex,1 as Class,80 FScore, 'Chinese'  as sub union 
select 'Fox' Name,'男' Sex,1 as Class,83 FScore,'English' as sub union
select 'Fox' Name,'男' Sex,1 as Class,85 FScore,'Maths'  as sub union 
select 'Xing' Name,'男' Sex,1 as Class,60 FScore,'Chinese' as sub union 
select 'Xing' Name,'男' Sex,1 as Class,73 FScore,'English' as sub union
select 'Xing' Name,'男' Sex,1 as Class,65 FScore,'Maths' as sub union 
select 'EN' Name,'女' Sex,1 as Class,69 FScore,'Chinese' as sub union 
select 'EN' Name,'女' Sex,1 as Class,76 FScore,'English' as sub union
select 'EN' Name,'女' Sex,1 as Class,95 FScore,'Maths' as sub union 
select 'May' Name,'女' Sex,2 as Class,89 FScore,'Chinese' as sub union 
select 'May' Name,'女' Sex,2 as Class,96 FScore,'English' as sub union
select 'May' Name,'女' Sex,2 as Class,92 FScore,'Maths' as sub
) a where 1=1

1.2、方法一利用MAX、group by实现行转换

DECLARE @sql VARCHAR(max)
SET @sql = 'SELECT [Name],[class],[sex]'
SELECT  @sql = @sql + ',MAX(CASE [sub] WHEN   ''' + [sub] + ''' THEN [FScore] ELSE ''0'' END)[' + [sub] + '] '
FROM    ( 
            SELECT DISTINCT [sub] FROM #XinTest
        ) T1
--同FROM tbScore  GROUP BY [课程]，默认按课程名排序
SET @sql = @sql + ',sum(FScore) 总分,cast(avg(FScore*1.0) as decimal(18,2)) 平均分 FROM #XinTest GROUP BY [Name],[class],[sex] 
order by Class'
PRINT '@sql: ' + @sql
EXEC(@sql)

1.3、行转列的用法PIVOT

-------------数据准备---------
SELECT 3 id,'桃子' name,1 quarter,1000 number
INTO #test

insert into #test values(1,N'苹果',1,1000)
insert into #test values(1,N'苹果',2,2000)
insert into #test values(1,N'苹果',3,4000)
insert into #test values(1,N'苹果',4,5000)
insert into #test values(2,N'梨子',1,3000)
insert into #test values(2,N'梨子',2,3500)
insert into #test values(2,N'梨子',3,4200)
insert into #test values(2,N'梨子',4,5500)


---------使用PIVOT实现行列转换---------

select ID,NAME,
[1] as '一季度',
[2] as '二季度',
[3] as '三季度',
[4] as '四季度'
from
#test
pivot
(
sum(number)
for quarter in
([1],[2],[3],[4])
)
as pvt

二、列转行

2.1 数据准备

SELECT 3 id,'桃子' name,1 quarter,1000 number
INTO #test
insert into #test values(1,N'苹果',1,1000)
insert into #test values(1,N'苹果',2,2000)
insert into #test values(1,N'苹果',3,4000)
insert into #test values(1,N'苹果',4,5000)
insert into #test values(2,N'梨子',1,3000)
insert into #test values(2,N'梨子',2,3500)
insert into #test values(2,N'梨子',3,4200)
insert into #test values(2,N'梨子',4,5500)


select ID,NAME,
[1] as '一季度',
[2] as '二季度',
[3] as '三季度',
[4] as '四季度'
INTO #tempB
FROM 
#test 
pivot
(
sum(number)
for quarter in
([1],[2],[3],[4])
)
as pvt

2.2 列转行例子

SELECT *
FROM #tempB;


SELECT id,
       name,
       quarter,
       number
FROM #tempB
    UNPIVOT
    (
        number
        FOR quarter IN ([一季度], [二季度], [三季度], [四季度])
    ) AS unpvt;

打赏赞