pivot行转列、unpivot列转行

 

 

【一：行转列】插入临时数据

--插入一下临时数据源
with m as(
    select '张三' name,'语文'course,'89'score union all
    select '张三' name,'数学'course,'100'score union all
    select '张三' name,'英语'course,'40'score union all
    select '张三' name,'物理'course,'93'score union all
    select '张三' name,'地理'course,'95'score union all
    select '张三' name,'化学'course,'80'score union all
    select '李四' name,'语文'course,'89'score union all
    select '李四' name,'数学'course,'100'score union all
    select '李四' name,'英语'course,'40'score union all
    select '李四' name,'物理'course,'93'score union all
    select '李四' name,'地理'course,'95'score union all
    select '李四' name,'化学'course,'80'score
) select * into #info from m

--查询临时数据
select * from #info

使用 pivot 行转列

select name, 地理,化学,数学,物理,英语,语文
from  (
    select name,score ,course from #info
)as A
pivot( 
　　max(score) for course in(地理,化学,数学,物理,英语,语文)
) as B

 

 

【二：列转行】继续使用上面行转列的数据做为数据源

--继续上面行转列的数据保存成数据源
select name, 地理,化学,数学,物理,英语,语文 into #info2
from  (
    select name,score,course from #info
)as A
pivot( 
    max(score) for course in(地理,化学,数学,物理,英语,语文)
) as B
--查询数据源
select * from #info2

使用unpivot进行列转行操作

--列转行
SELECT name, course, score
FROM(
    SELECT name,地理,化学,数学,物理,英语,语文 FROM  #info2 
)as A
unpivot(score  for course  IN  
       (地理,化学,数学,物理,英语,语文)
 ) AS  B

 

 

【三：动态行转列】使用刚才建立的#info、#info2临时表，做动态列语句拼接

--动态列生成
DECLARE @SqlCourse VARCHAR(max)
DECLARE @Sql VARCHAR(max)
select @SqlCourse = STUFF((SELECT ',' + course FROM ( select distinct course from #info )as A FOR XML PATH('')), 1, 1, '') --拆分列集合

--拼接行转列语句执行语句
select @Sql = 'select * from #info as A pivot(max(score) for course in('+@SqlCourse+')) as B ' 
PRINT @Sql
EXEC (@Sql)

--拼接列转行语句执行语句
select @Sql = 'SELECT * FROM #info2 as A unpivot(score  for course in ('+ @SqlCourse +')) AS B' --拼接执行语句
PRINT @Sql
EXEC (@Sql)