sql server行列转化和行列置换

时间:2022-02-22 02:35:14
  1. 行列转换:
  1. 姓名 课程 分数
  2. 张三 语文 74
  3. 张三 数学 83
  4. 张三 物理 93
  5. 李四 语文 74
  6. 李四 数学 84
  7. 李四 物理 94
  8. 想变成(得到如下结果):
  9. 姓名 语文 数学 物理
  10. ---- ---- ---- ----
  11. 李四 74   84   94
  12. 张三 74   83   93
  13. create table sc(姓名 varchar(10),课程 varchar(10),分数 float)
  14. insert into sc
  15. select '张三','语文',74
  16. union
  17. select '张三','数学',83
  18. union
  19. select '张三','物理',93
  20. union
  21. select '李四','语文',74
  22. union
  23. select '李四','数学',84
  24. union
  25. select '李四','物理',94
  26. 方法1:
  27. declare @sql varchar(max)
  28. set @sql='select '
  29. select @sql=@sql+', max(case when 课程='''+课程+''' then 分数 else '''' end)['+课程+']' from (select distinct 课程 from sc)t
  30. set @sql = STUFF(@sql,8,1,'')
  31. print @sql
  32. set @sql=@sql+' ,姓名 from sc group by 姓名'
  33. exec(@sql)
  34. 方法2:
  35. select 姓名,数学,物理,语文 from sc pivot( max(分数) for 课程 in(数学,物理,语文))t
  36. 方法3:
  37. declare @sql varchar(8000)
  38. select @sql = isnull(@sql + '],[' , '') + 课程 from sc group by 课程
  39. print @sql
  40. set @sql = '[' + @sql + ']'
  41. exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')

行列置换:

  1. 姓名  语文  数学   物理
  2. ----------------------------------
  3. 张三  80     90    85
  4. 李四  85     92    82
  5. 要求使用T-SQL语言实现以下结果:
  6. 课程  张三  李四
  7. ----------------------
  8. 语文  80    85
  9. 数学  90    92
  10. 物理  85    82
  11. drop table sc
  12. create table sc(姓名 varchar(10),语文 int,数学 int,物理 int)
  13. insert into sc
  14. select '张三',80,90,85
  15. union all
  16. select '李四',85,92,82
  17. select * from sc
  18. -------------这个过程不就是unpivot,有时间再补充?
  19. select * into sc1 from(
  20. select 姓名,'语文' 课程,语文 分数 from sc
  21. union
  22. select 姓名,'数学' 课程,数学 from sc
  23. union
  24. select 姓名,'物理' 课程,物理 from sc
  25. )t
  1. 补充unpivot,和上面操作时同样的效果
  1. select  姓名,课程,分数 into #sc1 from sc unpivot(分数 for 课程 in([语文],[数学],[物理]))a
  1. declare @sql varchar(8000)
  2. set @sql='select '
  3. select @sql=@sql+', max(case when 姓名='''+姓名+''' then 分数 else ''''end)['+姓名+']' from (select distinct 姓名 from
  4. sc1)t
  5. set @sql=stuff(@sql,8,1,'')
  6. set @sql=@sql+' ,课程 from sc1 group by 课程'
  7. print @sql
  8. exec(@sql)
  1. 补充动态pivot和unpiot
      1. --------------pivot
      2. declare @sql varchar(8000)
      3. select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
      4. print @sql
      5. set @sql = '[' + @sql + ']'
      6. exec ('select *  from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
      7. --------------unpivot
      8. declare @sql varchar(8000)
      9. select @sql = isnull(@sql + '],[' , '') + name from syscolumns where id=OBJECT_ID('tb') and colorder>1
      10. set @sql = '[' + @sql + ']'
      11. exec ('select 姓名,课程,分数 from (select * from tbtb) a unpivot (分数 for 课程 in (' + @sql + ')) b')