MSSQL 2005 列转行应用案例-白红宇

MSSQL 2005 列转行应用案例

阅读量：5919 次

发布时间：2019-06-19

本文共 9235 字，大约阅读时间需要 30 分钟。

/*MSSQL 2005 列转行应用案例 By claro(陈亮) 2008-12-2 转载请包含此信息*/

--test table KuCunMX

If object_id ('KuCunMX') is not NULL

Drop Table KuCunMX

else

Create Table KuCunMX (ShangPinBH varchar(20),ShiJian datetime,KuWeiBH varchar(20),ShuLiang int)

--新建库存表并插入数据

Insert Into KuCunMX

Select '0000000000001',getdate(),'A999990000',1 Union All

Select '0000000000002',getdate(),'A999990000',2 Union All

Select '0000000000003',getdate(),'A999990000',3 Union All

Select '0000000000004',getdate(),'A999990000',4 Union All

Select '0000000000005',getdate(),'A999990000',5 Union All

Select '0000000000006',getdate(),'E050000000',6 Union All

Select '0000000000007',getdate(),'E050000000',7 Union All

Select '0000000000008',getdate(),'E050000000',8 Union All

Select '0000000000009',getdate(),'E050000000',9 Union All

Select '0000000000010',getdate(),'E050000000',10

--Select * From KuCunMX

/*ShangPinBH ShiJian KuWeiBH ShuLiang

0000000000001 2008-12-02 23:42:11.543 A999990000 1

0000000000002 2008-12-02 23:42:11.543 A999990000 2

0000000000003 2008-12-02 23:42:11.543 A999990000 3

0000000000004 2008-12-02 23:42:11.543 A999990000 4

0000000000005 2008-12-02 23:42:11.543 A999990000 5

0000000000006 2008-12-02 23:42:11.543 E050000000 6

0000000000007 2008-12-02 23:42:11.543 E050000000 7

0000000000008 2008-12-02 23:42:11.543 E050000000 8

0000000000009 2008-12-02 23:42:11.543 E050000000 9

0000000000010 2008-12-02 23:42:11.543 E050000000 10

--预订储位的库存信息列转行

Select ShangPinBH,ISNULL(A999990000,0) A999990000,ISNULL(E050000000,0) E050000000

from (Select ShangPinBH,KuWeiBH,ShuLiang from KuCunMX ) a pivot (max(ShuLiang) for KuWeiBH

in (A999990000,E050000000)) b

ShangPinBH A999990000 E050000000

0000000000001 1 0

0000000000002 2 0

0000000000003 3 0

0000000000004 4 0

0000000000005 5 0

0000000000006 0 6

0000000000007 0 7

0000000000008 0 8

0000000000009 0 9

0000000000010 0 10

--test table ShangPin

If object_id('ShangPin') is not NULL

Drop Table ShangPin

Else

Create table ShangPin (ShangPinBH varchar(20),ShiJian datetime,ShangPinMC varchar(20),ISBN varchar(20),DingJia float)

--新建商品表并插入数据

Insert into ShangPin

Select '0000000000001',getdate(),'SQL2000入门经典','7-1111-1111-1',10 Union All

Select '0000000000002',getdate(),'SQL2005入门经典','7-1111-1111-2',20 Union All

Select '0000000000003',getdate(),'SQL2008入门经典','7-1111-1111-3',30 Union All

Select '0000000000004',getdate(),'OFFICE97入门经典','7-1111-1111-4',40 Union All

Select '0000000000005',getdate(),'OFFICE2000入门经典','7-1111-1111-5',50 Union All

Select '0000000000006',getdate(),'OFFICE2003入门经典','7-1111-1111-6',60 Union All

Select '0000000000007',getdate(),'OFFICE2007入门经典','7-1111-1111-7',70 Union All

Select '0000000000008',getdate(),'C#.NET入门经典','7-1111-1111-8',70 Union All

Select '0000000000009',getdate(),'Java入门经典','7-1111-1111-9',90 Union All

Select '0000000000010',getdate(),'正则表达式必知必会','7-1111-1111-0',100

--select * from shangpin

ShangPinBH ShiJian ShangPinMC ISBN DingJia

0000000000001 2008-12-02 23:47:30.107 SQL2000入门经典 7-1111-1111-1 10

0000000000002 2008-12-02 23:47:30.107 SQL2005入门经典 7-1111-1111-2 20

0000000000003 2008-12-02 23:47:30.107 SQL2008入门经典 7-1111-1111-3 30

0000000000004 2008-12-02 23:47:30.107 OFFICE97入门经典 7-1111-1111-4 40

0000000000005 2008-12-02 23:47:30.107 OFFICE2000入门经典 7-1111-1111-5 50

0000000000006 2008-12-02 23:47:30.107 OFFICE2003入门经典 7-1111-1111-6 60

0000000000007 2008-12-02 23:47:30.107 OFFICE2007入门经典 7-1111-1111-7 70

0000000000008 2008-12-02 23:47:30.107 C#.NET入门经典 7-1111-1111-8 70

0000000000009 2008-12-02 23:47:30.107 Java入门经典 7-1111-1111-9 90

0000000000010 2008-12-02 23:47:30.107 正则表达式必知必会 7-1111-1111-0 100

--(行列转换)显示预订储位的库存信息和商品信息

Select sp.ShangPinBH,sp.ShangPinMC,mx11.特殊储位A999990000,mx11.特殊储位E050000000

from ShangPin sp

INNER JOIN (Select ShangPinBH,ISNULL(A999990000,0) 特殊储位A999990000,ISNULL(E050000000,0) 特殊储位E050000000

from (Select ShangPinBH,KuWeiBH,ShuLiang from KuCunMX ) a pivot (max(ShuLiang) For KuWeiBH in (A999990000,E050000000)) b) mx11

on sp.ShangPinBH=mx11.ShangPinBH

ShangPinBH ShangPinMC 特殊储位A999990000 特殊储位E050000000

0000000000001 SQL2000入门经典 1 0

0000000000002 SQL2005入门经典 2 0

0000000000003 SQL2008入门经典 3 0

0000000000004 OFFICE97入门经典 4 0

0000000000005 OFFICE2000入门经典 5 0

0000000000006 OFFICE2003入门经典 0 6

0000000000007 OFFICE2007入门经典 0 7

0000000000008 C#.NET入门经典 0 8

0000000000009 Java入门经典 0 9

0000000000010 正则表达式必知必会 0 10

--END--go sleeping

--test table JinHuoD

If object_id ('JinHuoD') is not NULL

Drop Table JinHuoD

else

Create Table JinHuoD (JinHuoDBH varchar(20),ShiJian datetime,caozuoy varchar(20))

--新建进货单表并插入数据

Insert Into JinHuoD

Select 'JH2008120300001',getdate(),'admin' Union All

Select 'JH2008120200002',getdate()-1,'admin' Union All

Select 'JH2008120100003',getdate()-2,'admin'

--select * from JinHuoD

JinHuoDBH ShiJian caozuoy

JH2008120300001 2008-12-03 10:23:57.513 admin

JH2008120200002 2008-12-02 10:23:57.513 admin

JH2008120100003 2008-12-01 10:23:57.513 admin

--test table JinHuoDMX

If object_id ('JinHuoDMX') is not NULL

Drop Table JinHuoDMX

else

Create Table JinHuoDMX (JinHuoDBH varchar(20),JinHuoDMXBH varchar(40),ShangPinBH varchar(20),FuHeSL int)

--新建进货单明细表并插入数据

Insert Into JinHuoDMX

Select 'JH2008120300001','JH200812030000101','0000000000001',1 Union All

Select 'JH2008120300001','JH200812030000102','0000000000002',2 Union All

Select 'JH2008120300001','JH200812030000103','0000000000005',12 Union All

Select 'JH2008120300001','JH200812030000104','0000000000004',4 Union All

Select 'JH2008120300001','JH200812030000105','0000000000005',5 Union All

Select 'JH2008120300001','JH200812030000106','0000000000006',6 Union All

Select 'JH2008120300001','JH200812030000108','0000000000008',8 Union All

Select 'JH2008120300001','JH200812030000109','0000000000008',11 Union All

Select 'JH2008120300001','JH200812030000110','0000000000010',10 Union All

Select 'JH2008120200002','JH200812020000201','0000000000003',1 Union All

Select 'JH2008120200002','JH200812020000202','0000000000001',2 Union All

Select 'JH2008120100003','JH200812010000301','0000000000009',111

--select * from JinHuoDMX

JinHuoDBH JinHuoDMXBH ShangPinBH FuHeSL

JH2008120300001 JH200812030000101 0000000000001 1

JH2008120300001 JH200812030000102 0000000000002 2

JH2008120300001 JH200812030000103 0000000000005 12

JH2008120300001 JH200812030000104 0000000000004 4

JH2008120300001 JH200812030000105 0000000000005 5

JH2008120300001 JH200812030000106 0000000000006 6

JH2008120300001 JH200812030000108 0000000000008 8

JH2008120300001 JH200812030000109 0000000000008 11

JH2008120300001 JH200812030000110 0000000000010 10

JH2008120200002 JH200812020000201 0000000000003 1

JH2008120200002 JH200812020000202 0000000000001 2

JH2008120100003 JH200812010000301 0000000000009 111

--显示预订储位的库存信息,显示第一次进货时间

Select sp.ShangPinBH,sp.ShangPinMC,mx11.特殊储位A999990000,mx11.特殊储位E050000000,

ISNULL(CONVERT(VARCHAR(100),ys1.DaoHuoRQ,23),'无进货史') 第一次进货日期

from ShangPin sp

INNER JOIN (Select ShangPinBH,ISNULL(A999990000,0) 特殊储位A999990000,ISNULL(E050000000,0) 特殊储位E050000000

from (Select ShangPinBH,KuWeiBH,ShuLiang from KuCunMX ) a pivot (max(ShuLiang)

For KuWeiBH in (A999990000,E050000000)) b) mx11

on sp.ShangPinBH=mx11.ShangPinBH

LeFT JOIN (Select mx21.ShangPinBH,MIN(ys.ShiJian) DaoHuoRQ

from JinHuoD ys

INNER JOIN JinHuoDMX mx21 on mx21.JinHuoDBH=ys.JinHuoDBH

Group By mx21.ShangPinBH) ys1

on ys1.ShangPinBH=sp.ShangPinBH

ShangPinBH ShangPinMC 特殊储位A999990000 特殊储位E050000000 第一次进货日期

0000000000001 SQL2000入门经典 1 0 2008-12-02

0000000000002 SQL2005入门经典 2 0 2008-12-03

0000000000003 SQL2008入门经典 3 0 2008-12-02

0000000000004 OFFICE97入门经典 4 0 2008-12-03

0000000000005 OFFICE2000入门经典 5 0 2008-12-03

0000000000006 OFFICE2003入门经典 0 6 2008-12-03

0000000000007 OFFICE2007入门经典 0 7 无进货史

0000000000008 C#.NET入门经典 0 8 2008-12-03

0000000000009 Java入门经典 0 9 2008-12-01

0000000000010 正则表达式必知必会 0 10 2008-12-03

--显示预订储位的库存信息和商品信息以及第一次进货时间和累计进货数量

Select sp.ShangPinBH,sp.ShangPinMC,mx11.特殊储位A999990000,mx11.特殊储位E050000000,

ISNULL(CONVERT(VARCHAR(100),ys1.DaoHuoRQ,23),'无进货史') 第一次进货日期,

ISNULL(ys2.yanshoul,0) 累计进货数量

from ShangPin sp

INNER JOIN (Select ShangPinBH,ISNULL(A999990000,0) 特殊储位A999990000,ISNULL(E050000000,0) 特殊储位E050000000

from (Select ShangPinBH,KuWeiBH,ShuLiang from KuCunMX ) a pivot (max(ShuLiang)

For KuWeiBH in (A999990000,E050000000)) b) mx11

on sp.ShangPinBH=mx11.ShangPinBH

LeFT JOIN (Select mx21.ShangPinBH,MIN(ys.ShiJian) DaoHuoRQ

from JinHuoD ys

INNER JOIN JinHuoDMX mx21 on mx21.JinHuoDBH=ys.JinHuoDBH

Group By mx21.ShangPinBH) ys1

on ys1.ShangPinBH=sp.ShangPinBH

LeFT JOIN (Select mx31.ShangPinBH,SUM(mx31.FuHeSL) yanshoul

from JinHuoDMX mx31

INNER JOIN JinHuoD ys on mx31.JinHuoDBH=ys.JinHuoDBH

Group By mx31.ShangPinBH) ys2

on ys2.ShangPinBH=sp.ShangPinBH

ShangPinBH ShangPinMC 特殊储位A999990000 特殊储位E050000000 第一次进货日期累计进货数量

0000000000001 SQL2000入门经典 1 0 2008-12-02 3

0000000000002 SQL2005入门经典 2 0 2008-12-03 2

0000000000003 SQL2008入门经典 3 0 2008-12-02 1

0000000000004 OFFICE97入门经典 4 0 2008-12-03 4

0000000000005 OFFICE2000入门经典 5 0 2008-12-03 17

0000000000006 OFFICE2003入门经典 0 6 2008-12-03 6

0000000000007 OFFICE2007入门经典 0 7 无进货史 0

0000000000008 C#.NET入门经典 0 8 2008-12-03 19

0000000000009 Java入门经典 0 9 2008-12-01 111

0000000000010 正则表达式必知必会 0 10 2008-12-03 10

--END

转载于:https://www.cnblogs.com/accumulater/p/6101152.html

你可能感兴趣的文章

JavaScript语法

查看>>

T-SQL - 通过关联ID，将A表中多列ID关联为B表中的数据

Microsoft Sync Framework同步数据库 3：针对同步进行设置

查看>>

Exchange 2013 中部署边缘传输服务器

Exchange 2013 发送连接器和接收连接器配置

查看>>

我的友情链接

查看>>