sql 时间行列转换求总和

先看表结构

sql 时间行列转换求总和

USE [TestMyDB]
GO

/****** Object:  Table [dbo].[Product]    Script Date: 2015/9/25 16:09:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[Product](
    [ProductId] [VARCHAR](36) NOT NULL,
    [ProductName] [VARCHAR](50) NULL,
    [CreateDate] [DATETIME] NULL,
    [Price] [DECIMAL](18, 2) NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
    [ProductId] 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 ANSI_PADDING OFF
GO

插入语句

--INSERT INTO dbo.Product VALUES(NEWID(),'鸡蛋',GETDATE(),0.5)
--INSERT INTO dbo.Product VALUES(NEWID(),'鸡蛋',GETDATE(),0.5)
--INSERT INTO dbo.Product VALUES(NEWID(),'鸡蛋',GETDATE(),0.5)
--INSERT INTO dbo.Product VALUES(NEWID(),'鸡蛋',GETDATE(),0.5)
--INSERT INTO dbo.Product VALUES(NEWID(),'鸡蛋',GETDATE(),0.5)
--INSERT INTO dbo.Product VALUES(NEWID(),'鸡蛋',GETDATE(),0.5)
--INSERT INTO dbo.Product VALUES(NEWID(),'鸡蛋',GETDATE(),0.5)
--INSERT INTO dbo.Product VALUES(NEWID(),'鸡蛋',GETDATE(),0.5)



--INSERT INTO dbo.Product VALUES(NEWID(),'猪肉',GETDATE(),25.8)
--INSERT INTO dbo.Product VALUES(NEWID(),'猪肉',GETDATE(),25.8)
--INSERT INTO dbo.Product VALUES(NEWID(),'猪肉',GETDATE(),25.8)
--INSERT INTO dbo.Product VALUES(NEWID(),'猪肉',GETDATE(),25.8)
--INSERT INTO dbo.Product VALUES(NEWID(),'猪肉',GETDATE(),25.8)
--INSERT INTO dbo.Product VALUES(NEWID(),'猪肉',GETDATE(),25.8)


--INSERT INTO dbo.Product VALUES(NEWID(),'牛肉',GETDATE(),60.8)
--INSERT INTO dbo.Product VALUES(NEWID(),'牛肉',GETDATE(),60.8)
--INSERT INTO dbo.Product VALUES(NEWID(),'牛肉',GETDATE(),60.8)
--INSERT INTO dbo.Product VALUES(NEWID(),'牛肉',GETDATE(),60.1)

sql 时间行列转换求总和

时间行列转换求总和

sql 时间行列转换求总和

SELECT
        ProductName,
        SUM([1]+[2]+[3]+[4]+[5]+[6]+[24]) AS summary,
        [1] ,
        [2] ,
        [3] ,
        [4],
        [5],
        [6],
        [24]
        
from
(SELECT  --'当天价格总和' AS Cost_Sorted_By_Production_Days ,
        ProductName,
        ISNULL([1],0) AS [1],
        ISNULL([2],0) AS [2],
        ISNULL([3],0) AS [3],
        ISNULL([4],0) AS [4],
        ISNULL([5],0) AS [5],
        ISNULL([6],0) AS [6],
        ISNULL([24],0)AS [24]
FROM    ( SELECT    
DAY(CreateDate) CreateDate,
ProductName ,
SUM(Price) Price
FROM dbo.Product GROUP BY ProductName,day(CreateDate)
        ) AS SourceTable PIVOT
( SUM(Price) FOR CreateDate IN ( [1], [2], [3], [4],[5],[6],[24] ) ) AS PivotTable ) AS a  GROUP BY a.ProductName,a.[1],a.[2],a.[3],a.[4],a.[5],a.[6],a.[24]

sql 时间行列转换求总和

相关推荐