SQLSERVER行转列和列转行.docx

1： 行转列子查询，获取一定数据集结果SELECT objid,action,count(1) AS count FROM T_MyAttention WHERE objid IN(SELECT TOP 10 objid FROM T_MyAttention tma GROUP BY objid ORDER BY count(1) DESC)GROUP BY objid,action下面用 行转列语法获取 最终结果select *from (SELECT objid,action,count(1) AS count FROM T_MyAttention WHERE objid IN(SELECT TOP 10 objid FROM T_MyAttention tma GROUP BY objid ORDER BY count(1) DESC)GROUP BY objid,action) tpivot ( sum(count) for t.action in (1,2,3,4) as ourpivot微软官方的图：2： 列转行怎么把一条记录拆分成几条记录？User No. A B C1 1 21 34 241 2 42 25 16RESULT:User No. Type Num1 1 A 211 1 B 341 1 C 241 2 A 421 2 B 251 2 C 16declare t table(usser int ,no int ,a int,b int, c int)insert into t select 1,1,21,34,24union all select 1,2,42,25,16SELECT usser,no,Type=attribute, Num=valueFROM t UNPIVOT ( value FOR attribute IN(a, b, c) ) AS UPV列转行备注 value FOR attribute IN(a, b, c) 这句话中，a,b,c 是列的名字，但是列名不能出现在上句的select语句中。-结果/*usser no Type num- - - -1 1 a 211 1b341 1c 241 2a 421 2b251 2c 16*/T-SQL语句中，PIVOT命令可以实现数据表的列转行，UNPIVOT则与其相反，实现数据的行转列。本文结合实例说明了这一过程，希望能对您有所帮助。AD：WOT2015 互联网运维与开发者大会 热销抢票一、使用PIVOT和UNPIVOT命令的SQL Server版本要求1.数据库的最低版本要求为SQL Server 2005 或更高。2.必须将数据库的兼容级别设置为90 或更高。3.查看我的数据库版本及兼容级别。如果不知道怎么看数据库版本或兼容级别的话可以在SQL Server Management Studio新建一个查询窗口输入：print version，运行之后在我的本机上得到：Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)Apr 2 2010 15:53:02Copyright (c) Microsoft CorporationExpress Edition with Advanced Services on Windows NT 5.2 (Build 3790: Service Pack 2)然后我们选择一个数据库然后右键-属性 选择选项得到下图的信息。在确认数据库的版本和兼容级别符合1,2点的要求后你才可以接着继续往下学习。二、使用PIVOT 实现数据表的列转行1.在这里我们先构建一个测试数据表(这里使用的是临时表，以方便我们在退出会话的时候自动删除表及其数据)首先我们先设计一个表架构为#Student 学生编号PK, 姓名, 性别, 所属班级 的表,然后编写如下T-SQL-创建临时表(仅演示,表结构的不合理还请包涵)1. CREATETABLE#Student( 2. 3. 学生编号INTIDENTITY(1,1)PRIMARYKEY, 4. 5. 姓名NVARCHAR(20), 6. 7. 性别NVARCHAR(1), 8. 9. 所属班级NVARCHAR(20) 10. 11. );-给临时表插入数据1. INSERTINTO#Student( 2. 3. 姓名,性别,所属班级 4. 5. ) 6. 7. SELECT李妹妹,女,初一1班UNIONALL 8. 9. SELECT泰强,男,初一1班UNIONALL 10. 11. SELECT泰映,男,初一1班UNIONALL 12. 13. SELECT何谢,男,初一1班UNIONALL 14. 15. SELECT李春,男,初二1班UNIONALL 16. 17. SELECT吴歌,男,初二1班UNIONALL 18. 19. SELECT林纯,男,初二1班UNIONALL 20. 21. SELECT徐叶,女,初二1班UNIONALL 22. 23. SELECT龙门,男,初三1班UNIONALL 24. 25. SELECT小红,女,初三1班UNIONALL 26. 27. SELECT小李,男,初三1班UNIONALL 28. 29. SELECT小黄,女,初三2班UNIONALL 30. 31. SELECT旺财,男,初三2班UNIONALL 32. 33. SELECT强强,男,初二1班;以下是查询的结果：学生编号姓名性别所属班级1李妹妹女初一 1班2泰强男初一 1班3泰映男初一 1班4何谢男初一 1班5李春男初二 1班6吴歌男初二 1班7林纯男初二 1班8徐叶女初二 1班9龙门男初三 1班10小红女初三 1班11小李男初三 1班12小黄女初三 2班13旺财男初三 2班14强强男初二 1班2.查询各班级的总人数1. SELECT 2. 3. 所属班级AS班级, 4. 5. COUNT(1)AS人数 6. 7. FROM#Student 8. 9. GROUPBY所属班级 10. 11. ORDERBY人数DESC班级人数初二 1班5初一 1班4初三 1班3初三 2班2好了，在这里我希望把上面的表 班级, 人数 由 班级行 的显示转换为 班级列 的显示格式!在此你会看到第一个PIVOT示例。是否很期待?3.编写第一个PIVOT示例1. SELECT 2. 3. 班级总人数:AS总人数, 4. 5. 初一1班,初一2班, 6. 7. 初二1班, 8. 9. 初三1班,初三2班 10. 11. FROM( 12. 13. SELECT 14. 15. 所属班级AS班级, 16. 17. 学生编号 18. 19. FROM#Student 20. 21. )ASSourceTable 22. 23. PIVOT( 24. 25. COUNT(学生编号) 26. 27. FOR班级IN( 28. 29. 初一1班,初一2班, 30. 31. 初二1班, 32. 33. 初三1班,初三2班 34. 35. ) 36. 37. )ASPivotTable在结果表中我们看到了对于不存在的班级初一2班它的总人数为0，这符合我们预期的结果!解释：使用POVIT首先你需要在FROM子句内定义2个表：A.一个称为源表(SourceTable)。B.另一个称为数据透视表(PivotTable)。语法：1. SELECT 2. 3. , 4. 5. 第一个透视列AS, 6. 7. 第二个透视列AS, 8. 9. . 10. 11. 最后一个透视列AS12. 13. FROM( 14. 15. 16. 17. )AS18. 19. PIVOT( 20. 21. () 22. 23. FORIN( 24. 25. 第一个透视列,第二个透视列, 26. 27. . 28. 29. 最后一个透视列 30. 31. ) 32. 33. )AS34. 35. ;以上的PIVOT子句内的第1n个透视列的值均为需要转换为行的列的常量值,需要用括起，支持GUID，字符串及各种数字!4.下面演示一个较为高级的行转列的应用示例-使用PIVOT查询班级内的男女学生人数及总人数1. SELECT 2. 3. 所属班级AS班级, 4. 5. 男AS男生人数, 6. 7. 女AS女生人数, 8. 9. 男+女AS总人数 10. 11. FROM( 12. 13. SELECT学生编号,所属班级,性别FROM#Student 14. 15. )ASSourceTable 16. 17. PIVOT( 18. 19. COUNT(学生编号) 20. 21. FOR性别IN( 22. 23. 男,女 24. 25. ) 26. 27. )ASPivotTable 28. 29. ORDERBY总人数DESC三、使用UNPIVOT 实现的功能其实与PIVOT恰恰相反1.语法同PIVOT但是UNPIVOT的子句没有聚合函数1. SELECT 2. 3. , 4. 5. 合并后的列AS, 6. 7. 行值的列名AS8. 9. FROM( 10. 11. 12. 13. )AS14. 15. UNPIVOT( 16. 17. 18. 19. FORIN( 20. 21. 第一个合并列,第二个合并列, 22. 23. . 24. 25. 最后一个合并列 26. 27. ) 28. 29. )AS30. 31. ;2.看上面的语法感觉很浮云，不怕，这里带例子(继续使用II中用到的PIVOT表)-源表1. SELECT 2. 3. 班级总人数:AS总人数, 4. 5. 初一1班,初一2班, 6. 7. 初二1班, 8. 9. 初三1班,初三2班 10. 11. INTO#PivotTable-为了使表达意图更清晰,我把PIVOT处理后的表放到一个临时表当中 12. 13. FROM( 14. 15. SELECT 16. 17. 所属班级AS班级, 18. 19. 学生编号 20. 21. FROM#Student 22. 23. )ASSourceTable 24. 25. PIVOT( 26. 27. COUNT(学生编号) 28. 29. FOR班级IN( 30. 31. 初一1班,初一2班, 32. 33. 初二1班, 34. 35. 初三1班,初三2班 36. 37. ) 38. 39. )ASPivotTable将多个列合并到单个列的转换的语句!-结果1. SELECT 2. 3. 班级,总人数 4. 5. FROM( 6. 7. SELECT 8. 9. 初一1班,初一2班, 10. 11. 初二1班, 12. 13. 初三1班,初三2班 14. 15. FROM 16. 17. #PivotTable 18. 19. )ASs 20. 21. UNPIVOT( 22. 23. 总人数 24. 25. FOR班级IN( 26. 27. 初一1班,初一2班, 28. 29. 初二1班, 30. 31. 初三1班,初三2班 32. 33. ) 34. 35. )ASun_p执行下面代码：1. SELECT 2. 3. 所属班级AS班级, 4. 5. 男AS男生人数, 6. 7. 女AS女生人数, 8. 9. 男+女AS总人数 10. 11. INTO#PivotTable2-放到临时表方便查询 12. 13. FROM( 14. 15. SELECT学生编号,所属班级,性别FROM#Student 16. 17. )ASSourceTable 18. 19. PIVOT( 20. 21. COUNT(学生编号) 22. 23. FOR性别IN( 24. 25. 男,女 26. 27. ) 28. 29. )ASPivotTable 30. 31. ORDERBY总人数DESC 32. 33. SELECT 34. 35. 班级, 36. 37. 男生或女生人数, 38. 39. 性别, 40. 41. 总人数 42. 43. FROM( 44. 45. SELECT班级,男生人数,女生人数,总人数FROM#PivotTable2 46. 47. )ASs 48. 49. UNPIVOT( 50. 51. 男生或女生人数 52. 53. FOR性别IN( 54. 55. 男生人数, 56. 57. 女生人数 58. 59. ) 60. 61. )ASun_p或者将性别和人数合并到一个列当中：1. SELECT 2. 3. 班级, 4. 5. 性别+:+CAST(男生或女生人数ASNVARCHAR(1)AS男生或女生人数, 6. 7