27,582
社区成员




IF OBJECT_ID('t_p','u')IS NOT NULL
DROP TABLE t_p
GO
---建表
CREATE TABLE t_p(
Pid VARCHAR(20) NULL,--产品ID
cid VARCHAR(20) NULL,--客户ID
NUMS NUMERIC(18,0) NULL,--数量
DATES VARCHAR(20) NULL --日期
)
GO
---插入测试数据
INSERT INTO t_p
(
Pid,
cid,
NUMS,
DATES
)
SELECT 'A' AS N'产品ID','E' AS N'客户ID',2 AS N'数量','2015-06-01' AS N'日期' UNION ALL
SELECT 'A','E',3,'2015-06-02' UNION ALL
SELECT 'A','E',4,'2015-06-01' UNION ALL
SELECT 'A','E',5,'2015-06-02' UNION ALL
SELECT 'A','E',2,'2015-06-03' UNION ALL
SELECT 'A','E',3,'2015-06-04' UNION ALL
SELECT 'A','E',4,'2015-06-05' UNION ALL
SELECT 'A','E',5,'2015-06-06' UNION ALL
SELECT 'A','E',2,'2015-06-07' UNION ALL
SELECT 'A','E',2,'2015-06-08' UNION ALL
SELECT 'A','E',2,'2015-06-09' UNION ALL
SELECT 'A','E',2,'2015-06-10' UNION ALL
SELECT 'A','E',2,'2015-06-11' UNION ALL
SELECT 'A','E',2,'2015-06-10'
-----动态PIVOT行转列
DECLARE @sql_str VARCHAR(8000)
DECLARE @sql_col VARCHAR(8000)
SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(DATES) FROM t_p GROUP BY DATES
SET @sql_str = '
SELECT * FROM (
SELECT [Pid],[cid],
[NUMS],[DATES]
FROM [t_p]
) p PIVOT
(SUM([NUMS]) FOR [DATES] IN ( '+ @sql_col +') ) AS pvt
ORDER BY pvt.[Pid]'
---打印出动态SQL,测试SQL的正确性(可注释)
--PRINT (@sql_str)
---执行SQL
EXEC (@sql_str)
要到月底的话,需要在动态SQL之前做匹配处理,比如现在是2015-06-15,那你要到2015-06-30这一列,就要做日期匹配了,先查看,表中2015年6月是不是存在1到30号,不存在的要插入或者其他处理,让数据为零的日期显示出来DECLARE @SQL VARCHAR(8000)
DECLARE @StartDate VARCHAR(10),@EndDate VARCHAR(10)
SET @StartDate='2015-06-01'--查六月
SET @EndDate=CONVERT(VARCHAR(10),DATEADD(MONTH,1,@StartDate),120)
SET @SQL='SELECT 产品id,客户ID'
SELECT @SQL=@SQL+',SUM(CASE WHEN 日期='''+DateStr+''' THEN 数量 END)['+DateStr+']'
FROM(
SELECT CONVERT(VARCHAR(10),DATEADD(DAY,number,@StartDate),120)DateStr
FROM master..spt_values
WHERE type='P'AND number<DATEDIFF(DAY,@StartDate,@EndDate)
)T
SET @SQL=@SQL+',SUM(数量)[合计]
FROM TB
WHERE 日期>='''+@StartDate+''' AND 日期<'''+@EndDate+'''
GROUP BY 产品id,客户ID'
--PRINT @SQL
EXEC(@SQL)