实现行转列

来源：互联网发布：汉仪小麦体 mac 编辑：程序博客网时间：2024/06/06 07:25

1. /*

3. a

4. name objec score

5. a EN 89

6. a CH 78

7. a HO 99

8. b EN 34

9. b CH 88

10. b HO 66

11. 要求输出结果为：

12. name EN CH HO

13. a 89 78 99

14. b 34 88 66

15. */

16.

17. USE tempdb

18. IF (SELECT 1 FROM sys.sysobjects WHERE name ='t' AND type='u') is NOT NULL

19. DROP TABLE t

20.

21.

22. CREATE TABLE t

23. (

24. NAME CHAR(10),

25. objec CHAR(10),

26. score FLOAT

27. )

28. INSERT INTO t

29. VALUES('a','EN',89),('a','CH',78),('a','HO',99),('b','EN',34),('b','CH',88),('b','HO',66)

30.

31. SELECT * FROM T

32.

33. SELECT NAME ,MAX(CASE objec WHEN 'EN' THEN score ELSE 0 END ) EN,MAX(CASE objec WHEN 'CH' THEN score ELSE 0 END ) CH,MAX(CASE objec WHEN 'HO' THEN score ELSE 0 END ) HO

34. FROM t

35. GROUP BY NAME

36. --=======================================================================================================================================================

37. /*

38. 实现行转列

39. a

40. name objec score

41. a EN 89

42. a CH 78

43. a HO 99

44. b EN 34

45. b CH 88

46. b HO 66

47. 要求输出结果为：

48. name objec totalsorce

49. a EN,CH,HO 266

50. b EN,CH,HO 188

51. */

52.

53. USE tempdb

54. IF (SELECT 1 FROM sys.sysobjects WHERE name ='t' AND type='u') is NOT NULL

55. DROP TABLE t

56.

57. CREATE TABLE t

58. (

59. NAME CHAR(10),

60. objec CHAR(10),

61. score FLOAT

62. )

63. INSERT INTO t

64. VALUES('a','EN',89),('a','CH',78),('a','HO',99),('b','EN',34),('b','CH',88),('b','HO',66)

65.

66. SELECT * FROM T

67.

68. --为了去除objec中的最后一个逗号，插入一个临时表，并使用substring处理

69. SELECT name ,(SELECT LTRIM(RTRIM(objec))+',' FROM T WHERE objec=t.objec FOR XML PATH('')) objec,SUM(temp.score) totalscroe INTO #tmp

70. FROM T temp

71. GROUP BY name

72.

73.

74. SELECT NAME ,SUBSTRING(objec,0,LEN(objec)-1) objec ,totalscroe

75. FROM #tmp