首页 > 其他 > 详细

pivot-行转列示例2

时间:2017-12-16 16:44:20      阅读:223      评论:0      收藏:0      [点我收藏+]
USE [ExampleDb]
GO
/****** Object:  Table [dbo].[PivotDemo1]    Script Date: 2017/12/16 14:12:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PivotDemo1](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [WarehouseType] [nvarchar](50) NOT NULL CONSTRAINT [DF_Table_1_FType]  DEFAULT (‘‘),
    [Warehouse] [nvarchar](50) NOT NULL CONSTRAINT [DF_Demo_B仓库]  DEFAULT (‘‘),
    [Number] [int] NOT NULL CONSTRAINT [DF_Demo_C数量]  DEFAULT ((0)),
 CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[PivotDemo1] ON 

INSERT [dbo].[PivotDemo1] ([Id], [WarehouseType], [Warehouse], [Number]) VALUES (1, NAH125, NS库, 100)
INSERT [dbo].[PivotDemo1] ([Id], [WarehouseType], [Warehouse], [Number]) VALUES (2, NAH125, NH库, 200)
INSERT [dbo].[PivotDemo1] ([Id], [WarehouseType], [Warehouse], [Number]) VALUES (3, NAH125, NA库, 300)
INSERT [dbo].[PivotDemo1] ([Id], [WarehouseType], [Warehouse], [Number]) VALUES (4, NAH125, NB库, 400)
INSERT [dbo].[PivotDemo1] ([Id], [WarehouseType], [Warehouse], [Number]) VALUES (5, NAH126, NS库, 100)
INSERT [dbo].[PivotDemo1] ([Id], [WarehouseType], [Warehouse], [Number]) VALUES (6, NAH126, NH库, 300)
INSERT [dbo].[PivotDemo1] ([Id], [WarehouseType], [Warehouse], [Number]) VALUES (7, NAH126, NA库, 500)
INSERT [dbo].[PivotDemo1] ([Id], [WarehouseType], [Warehouse], [Number]) VALUES (8, NAH126, NB库, 700)
INSERT [dbo].[PivotDemo1] ([Id], [WarehouseType], [Warehouse], [Number]) VALUES (9, NAH127, NB库, 700)
SET IDENTITY_INSERT [dbo].[PivotDemo1] OFF

--------------------------------------------------------------------------------------
select  [Id] ,
        [WarehouseType] ,
        [Warehouse] ,
        [Number]
from    [ExampleDb].[dbo].[PivotDemo1];
--------------------------------------------------------------------------------------
select  *
from    ( select    Number ,
                    Warehouse ,
                    WarehouseType
          from      ExampleDb.dbo.PivotDemo1
        ) p pivot
( sum(Number) for Warehouse in ( S库, H库, A库, B库 ) ) as pvt
order by pvt.WarehouseType;
--------------------------------------------------------------------------------------

with    a as ( select   WarehouseType ,
                        S库 ,
                        H库 ,
                        A库 ,
                        B库
               from     ( select    Number ,
                                    Warehouse ,
                                    WarehouseType
                          from      ExampleDb.dbo.PivotDemo1
                        ) p pivot
( sum(Number) for Warehouse in ( S库, H库, A库, B库 ) ) as pvt
             ),
        b as ( select   WarehouseType as mo ,
                        sum(Number) as total
               from     ExampleDb.dbo.PivotDemo1
               group by WarehouseType
             )
    select  a.* ,
            b.total
    from    a
            join b on a.WarehouseType = b.mo;
--------------------------------------------------------------------------------------

 

pivot-行转列示例2

原文:http://www.cnblogs.com/huruiyi/p/8046748.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!