正文

MSSQL 2005 列转行应用案例

IP云
IP云V管理员/3 阅读
10/28
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示
  1. /*MSSQL 2005 列转行应用案例 By claro(陈亮) 2008-12-2 转载请包含此信息*/
  2. --test table KuCunMX
  3. If object_id ('KuCunMX') is not NULL
  4. Drop Table KuCunMX
  5. else
  6. Create Table KuCunMX (ShangPinBH varchar(20),ShiJian datetime,KuWeiBH varchar(20),ShuLiang int)
  7. Go
  8. --新建库存表并插入数据
  9. Insert Into KuCunMX
  10. Select '0000000000001',getdate(),'A999990000',1 Union All
  11. Select '0000000000002',getdate(),'A999990000',2 Union All
  12. Select '0000000000003',getdate(),'A999990000',3 Union All
  13. Select '0000000000004',getdate(),'A999990000',4 Union All
  14. Select '0000000000005',getdate(),'A999990000',5 Union All
  15. Select '0000000000006',getdate(),'E050000000',6 Union All
  16. Select '0000000000007',getdate(),'E050000000',7 Union All
  17. Select '0000000000008',getdate(),'E050000000',8 Union All
  18. Select '0000000000009',getdate(),'E050000000',9 Union All
  19. Select '0000000000010',getdate(),'E050000000',10
  20. --Select * From KuCunMX
  21. /*ShangPinBH    ShiJian                 KuWeiBH     ShuLiang
  22. 0000000000001   2008-12-02 23:42:11.543 A999990000  1
  23. 0000000000002   2008-12-02 23:42:11.543 A999990000  2
  24. 0000000000003   2008-12-02 23:42:11.543 A999990000  3
  25. 0000000000004   2008-12-02 23:42:11.543 A999990000  4
  26. 0000000000005   2008-12-02 23:42:11.543 A999990000  5
  27. 0000000000006   2008-12-02 23:42:11.543 E050000000  6
  28. 0000000000007   2008-12-02 23:42:11.543 E050000000  7
  29. 0000000000008   2008-12-02 23:42:11.543 E050000000  8
  30. 0000000000009   2008-12-02 23:42:11.543 E050000000  9
  31. 0000000000010   2008-12-02 23:42:11.543 E050000000  10
  32. */
  33. --预订储位的库存信息列转行
  34. Select ShangPinBH,ISNULL(A999990000,0) A999990000,ISNULL(E050000000,0) E050000000
  35. from (Select ShangPinBH,KuWeiBH,ShuLiang from KuCunMX ) a pivot (max(ShuLiang) for KuWeiBH
  36. in (A999990000,E050000000)) b
  37. /*
  38. ShangPinBH  A999990000  E050000000
  39. 0000000000001   1   0
  40. 0000000000002   2   0
  41. 0000000000003   3   0
  42. 0000000000004   4   0
  43. 0000000000005   5   0
  44. 0000000000006   0   6
  45. 0000000000007   0   7
  46. 0000000000008   0   8
  47. 0000000000009   0   9
  48. 0000000000010   0   10
  49. */
  50. --test table ShangPin
  51. If object_id('ShangPin') is not NULL
  52. Drop Table ShangPin
  53. Else
  54. Create table ShangPin (ShangPinBH varchar(20),ShiJian datetime,ShangPinMC varchar(20),ISBN varchar(20),DingJia float)
  55. Go
  56. --新建商品表并插入数据
  57. Insert into ShangPin
  58. Select '0000000000001',getdate(),'SQL2000入门经典','7-1111-1111-1',10 Union All
  59. Select '0000000000002',getdate(),'SQL2005入门经典','7-1111-1111-2',20 Union All
  60. Select '0000000000003',getdate(),'SQL2008入门经典','7-1111-1111-3',30 Union All
  61. Select '0000000000004',getdate(),'OFFICE97入门经典','7-1111-1111-4',40 Union All
  62. Select '0000000000005',getdate(),'OFFICE2000入门经典','7-1111-1111-5',50 Union All
  63. Select '0000000000006',getdate(),'OFFICE2003入门经典','7-1111-1111-6',60 Union All
  64. Select '0000000000007',getdate(),'OFFICE2007入门经典','7-1111-1111-7',70 Union All
  65. Select '0000000000008',getdate(),'C#.NET入门经典','7-1111-1111-8',70 Union All
  66. Select '0000000000009',getdate(),'Java入门经典','7-1111-1111-9',90 Union All
  67. Select '0000000000010',getdate(),'正则表达式必知必会','7-1111-1111-0',100
  68. --select * from shangpin
  69. /*
  70. ShangPinBH      ShiJian                 ShangPinMC          ISBN            DingJia
  71. 0000000000001   2008-12-02 23:47:30.107 SQL2000入门经典     7-1111-1111-1   10
  72. 0000000000002   2008-12-02 23:47:30.107 SQL2005入门经典     7-1111-1111-2   20
  73. 0000000000003   2008-12-02 23:47:30.107 SQL2008入门经典     7-1111-1111-3   30
  74. 0000000000004   2008-12-02 23:47:30.107 OFFICE97入门经典    7-1111-1111-4   40
  75. 0000000000005   2008-12-02 23:47:30.107 OFFICE2000入门经典  7-1111-1111-5   50
  76. 0000000000006   2008-12-02 23:47:30.107 OFFICE2003入门经典  7-1111-1111-6   60
  77. 0000000000007   2008-12-02 23:47:30.107 OFFICE2007入门经典  7-1111-1111-7   70
  78. 0000000000008   2008-12-02 23:47:30.107 C#.NET入门经典      7-1111-1111-8   70
  79. 0000000000009   2008-12-02 23:47:30.107 Java入门经典        7-1111-1111-9   90
  80. 0000000000010   2008-12-02 23:47:30.107 正则表达式必知必会   7-1111-1111-0   100
  81. */
  82. --(行列转换)显示预订储位的库存信息和商品信息
  83. Select sp.ShangPinBH,sp.ShangPinMC,mx11.特殊储位A999990000,mx11.特殊储位E050000000
  84. from ShangPin sp
  85. INNER JOIN (Select ShangPinBH,ISNULL(A999990000,0) 特殊储位A999990000,ISNULL(E050000000,0) 特殊储位E050000000
  86. from (Select ShangPinBH,KuWeiBH,ShuLiang from KuCunMX ) a pivot (max(ShuLiang) For KuWeiBH in (A999990000,E050000000)) b) mx11
  87. on sp.ShangPinBH=mx11.ShangPinBH
  88. /*
  89. ShangPinBH      ShangPinMC          特殊储位A999990000  特殊储位E050000000
  90. 0000000000001   SQL2000入门经典     1   0
  91. 0000000000002   SQL2005入门经典     2   0
  92. 0000000000003   SQL2008入门经典     3   0
  93. 0000000000004   OFFICE97入门经典    4   0
  94. 0000000000005   OFFICE2000入门经典  5   0
  95. 0000000000006   OFFICE2003入门经典  0   6
  96. 0000000000007   OFFICE2007入门经典  0   7
  97. 0000000000008   C#.NET入门经典      0   8
  98. 0000000000009   Java入门经典        0   9
  99. 0000000000010   正则表达式必知必会   0   10
  100. */
  101. --END--go sleeping
  102. --test table JinHuoD
  103. If object_id ('JinHuoD') is not NULL
  104. Drop Table JinHuoD
  105. else
  106. Create Table JinHuoD (JinHuoDBH varchar(20),ShiJian datetime,caozuoy varchar(20))
  107. Go
  108. --新建进货单表并插入数据
  109. Insert Into JinHuoD
  110. Select 'JH2008120300001',getdate(),'admin' Union All
  111. Select 'JH2008120200002',getdate()-1,'admin' Union All
  112. Select 'JH2008120100003',getdate()-2,'admin'
  113. --select * from JinHuoD
  114. /*
  115. JinHuoDBH       ShiJian                 caozuoy
  116. JH2008120300001 2008-12-03 10:23:57.513 admin
  117. JH2008120200002 2008-12-02 10:23:57.513 admin
  118. JH2008120100003 2008-12-01 10:23:57.513 admin
  119. */
  120. --test table JinHuoDMX
  121. If object_id ('JinHuoDMX') is not NULL
  122. Drop Table JinHuoDMX
  123. else
  124. Create Table JinHuoDMX (JinHuoDBH varchar(20),JinHuoDMXBH varchar(40),ShangPinBH varchar(20),FuHeSL int)
  125. Go
  126. --新建进货单明细表并插入数据
  127. Insert Into JinHuoDMX
  128. Select 'JH2008120300001','JH200812030000101','0000000000001',1 Union All
  129. Select 'JH2008120300001','JH200812030000102','0000000000002',2 Union All
  130. Select 'JH2008120300001','JH200812030000103','0000000000005',12 Union All
  131. Select 'JH2008120300001','JH200812030000104','0000000000004',4 Union All
  132. Select 'JH2008120300001','JH200812030000105','0000000000005',5 Union All
  133. Select 'JH2008120300001','JH200812030000106','0000000000006',6 Union All
  134. Select 'JH2008120300001','JH200812030000108','0000000000008',8 Union All
  135. Select 'JH2008120300001','JH200812030000109','0000000000008',11 Union All
  136. Select 'JH2008120300001','JH200812030000110','0000000000010',10 Union All
  137. Select 'JH2008120200002','JH200812020000201','0000000000003',1 Union All
  138. Select 'JH2008120200002','JH200812020000202','0000000000001',2 Union All
  139. Select 'JH2008120100003','JH200812010000301','0000000000009',111
  140. --select * from JinHuoDMX
  141. /*
  142. JinHuoDBH       JinHuoDMXBH         ShangPinBH      FuHeSL
  143. JH2008120300001 JH200812030000101   0000000000001   1
  144. JH2008120300001 JH200812030000102   0000000000002   2
  145. JH2008120300001 JH200812030000103   0000000000005   12
  146. JH2008120300001 JH200812030000104   0000000000004   4
  147. JH2008120300001 JH200812030000105   0000000000005   5
  148. JH2008120300001 JH200812030000106   0000000000006   6
  149. JH2008120300001 JH200812030000108   0000000000008   8
  150. JH2008120300001 JH200812030000109   0000000000008   11
  151. JH2008120300001 JH200812030000110   0000000000010   10
  152. JH2008120200002 JH200812020000201   0000000000003   1
  153. JH2008120200002 JH200812020000202   0000000000001   2
  154. JH2008120100003 JH200812010000301   0000000000009   111
  155. */
  156. --显示预订储位的库存信息,显示第一次进货时间
  157. Select sp.ShangPinBH,sp.ShangPinMC,mx11.特殊储位A999990000,mx11.特殊储位E050000000,
  158. ISNULL(CONVERT(VARCHAR(100),ys1.DaoHuoRQ,23),'无进货史') 第一次进货日期
  159. from ShangPin sp
  160. INNER JOIN (Select ShangPinBH,ISNULL(A999990000,0) 特殊储位A999990000,ISNULL(E050000000,0) 特殊储位E050000000
  161. from (Select ShangPinBH,KuWeiBH,ShuLiang from KuCunMX ) a pivot (max(ShuLiang)
  162. For KuWeiBH in (A999990000,E050000000)) b) mx11
  163. on sp.ShangPinBH=mx11.ShangPinBH
  164. LeFT JOIN (Select mx21.ShangPinBH,MIN(ys.ShiJian) DaoHuoRQ
  165. from JinHuoD ys
  166. INNER JOIN JinHuoDMX mx21 on mx21.JinHuoDBH=ys.JinHuoDBH
  167. Group By mx21.ShangPinBH) ys1
  168. on ys1.ShangPinBH=sp.ShangPinBH
  169. /*
  170. ShangPinBH      ShangPinMC          特殊储位A999990000  特殊储位E050000000  第一次进货日期
  171. 0000000000001   SQL2000入门经典     1                   0                   2008-12-02
  172. 0000000000002   SQL2005入门经典     2                   0                   2008-12-03
  173. 0000000000003   SQL2008入门经典     3                   0                   2008-12-02
  174. 0000000000004   OFFICE97入门经典    4                   0                   2008-12-03
  175. 0000000000005   OFFICE2000入门经典  5                   0                   2008-12-03
  176. 0000000000006   OFFICE2003入门经典  0                   6                   2008-12-03
  177. 0000000000007   OFFICE2007入门经典  0                   7                   无进货史
  178. 0000000000008   C#.NET入门经典      0                   8                   2008-12-03
  179. 0000000000009   Java入门经典        0                   9                   2008-12-01
  180. 0000000000010   正则表达式必知必会   0                   10                  2008-12-03
  181. */
  182. --显示预订储位的库存信息和商品信息以及第一次进货时间和累计进货数量
  183. Select sp.ShangPinBH,sp.ShangPinMC,mx11.特殊储位A999990000,mx11.特殊储位E050000000,
  184. ISNULL(CONVERT(VARCHAR(100),ys1.DaoHuoRQ,23),'无进货史') 第一次进货日期,
  185. ISNULL(ys2.yanshoul,0) 累计进货数量
  186. from ShangPin sp
  187. INNER JOIN (Select ShangPinBH,ISNULL(A999990000,0) 特殊储位A999990000,ISNULL(E050000000,0) 特殊储位E050000000
  188. from (Select ShangPinBH,KuWeiBH,ShuLiang from KuCunMX ) a pivot (max(ShuLiang)
  189. For KuWeiBH in (A999990000,E050000000)) b) mx11
  190. on sp.ShangPinBH=mx11.ShangPinBH
  191. LeFT JOIN (Select mx21.ShangPinBH,MIN(ys.ShiJian) DaoHuoRQ
  192. from JinHuoD ys
  193. INNER JOIN JinHuoDMX mx21 on mx21.JinHuoDBH=ys.JinHuoDBH
  194. Group By mx21.ShangPinBH) ys1
  195. on ys1.ShangPinBH=sp.ShangPinBH
  196. LeFT JOIN (Select mx31.ShangPinBH,SUM(mx31.FuHeSL) yanshoul
  197. from JinHuoDMX mx31
  198. INNER JOIN JinHuoD ys on mx31.JinHuoDBH=ys.JinHuoDBH
  199. Group By mx31.ShangPinBH) ys2
  200. on ys2.ShangPinBH=sp.ShangPinBH
  201. /*
  202. ShangPinBH      ShangPinMC          特殊储位A999990000  特殊储位E050000000  第一次进货日期 累计进货数量
  203. 0000000000001   SQL2000入门经典     1                   0                   2008-12-02      3
  204. 0000000000002   SQL2005入门经典     2                   0                   2008-12-03      2
  205. 0000000000003   SQL2008入门经典     3                   0                   2008-12-02      1
  206. 0000000000004   OFFICE97入门经典    4                   0                   2008-12-03      4
  207. 0000000000005   OFFICE2000入门经典  5                   0                   2008-12-03      17
  208. 0000000000006   OFFICE2003入门经典  0                   6                   2008-12-03      6
  209. 0000000000007   OFFICE2007入门经典  0                   7                   无进货史        0
  210. 0000000000008   C#.NET入门经典      0                   8                   2008-12-03      19
  211. 0000000000009   Java入门经典        0                   9                   2008-12-01      111
  212. 0000000000010   正则表达式必知必会   0                   10                  2008-12-03      10
  213. */
  214. --END