tsql - 以不同的方式旋转
问题描述
我想知道我是否可以以不同的方式进行调整,现在这就是我得到的:
SELECT [1] as semana1, [2] as semana2, [3] as semana3, [4] as semana4, [5] as semana5 , [BZ/TDZ]
FROM (
SELECT { fn WEEK(SCHEMA.SORDERQ.ORDDAT_0) } AS Semana, SCHEMA.ITMMASTER.TSICOD_3 as 'BZ/TDZ', SUM(SCHEMA.SORDERQ.QTY_0 * (SCHEMA.SORDERP.NETPRINOT_0 * SCHEMA.SORDER.CHGRAT_0))
AS [Total A.I]
FROM SCHEMA.SORDERQ INNER JOIN
SCHEMA.SORDER ON SCHEMA.SORDERQ.SOHNUM_0 = SCHEMA.SORDER.SOHNUM_0 INNER JOIN
SCHEMA.ITMMASTER ON SCHEMA.SORDERQ.ITMREF_0 = SCHEMA.ITMMASTER.ITMREF_0 RIGHT OUTER JOIN
SCHEMA.SORDERP ON SCHEMA.SORDERQ.SOPLIN_0 = SCHEMA.SORDERP.SOPLIN_0 AND SCHEMA.SORDER.SOHNUM_0 = SCHEMA.SORDERP.SOHNUM_0
WHERE (SCHEMA.SORDERQ.CPY_0 = N'BZES') AND (YEAR(SCHEMA.SORDERQ.ORDDAT_0) = YEAR(GETDATE()))
GROUP BY SCHEMA.ITMMASTER.TSICOD_3, { fn WEEK(SCHEMA.SORDERQ.ORDDAT_0) }) as DT
PIVOT (sum([Total A.I]) FOR Semana IN ([1], [2], [3], [4], [5]))
as PT
哪个返回
这是我得到的输出,'Semana1' 表示 Week1
可以以某种方式命令它得到这样的东西:
BZ TDZ
Week1 93150.94 425902.09
Week2
Week3
这主要是因为我按周订购,一年中有很多周。我拥有它的方式输出非常大(水平)
谢谢 !
解决方案
您基本上是在内部选择中进行的。
我已经清理了一下,只是将总和分为 2 个不同的总和,一个用于 BZ,一个用于 TDZ
并且不要使用 {fn week(...},只需使用
datepart(week,SCHEMA.SORDERQ.ORDDAT_0)
或者如果您在欧盟使用
datepart(ISO_WEEK,SCHEMA.SORDERQ.ORDDAT_0)
这是完整的查询
SELECT
'Semana' + Right('0'+STR(DATEPART(WEEK, [SCHEMA].SORDERQ.ORDDAT_0)),2) AS Semana
,SUM(IIF([SCHEMA].ITMMASTER.TSICOD_3 = 'BZ'
, [SCHEMA].SORDERQ.QTY_0 * [SCHEMA].SORDERP.NETPRINOT_0 * [SCHEMA].SORDER.CHGRAT_0, 0)
) BZ
,SUM(IIF([SCHEMA].ITMMASTER.TSICOD_3 = 'TDZ'
, [SCHEMA].SORDERQ.QTY_0 * [SCHEMA].SORDERP.NETPRINOT_0 * [SCHEMA].SORDER.CHGRAT_0, 0)
) TDZ
FROM [SCHEMA].SORDERQ
INNER JOIN [SCHEMA].SORDER
ON [SCHEMA].SORDERQ.SOHNUM_0 = [SCHEMA].SORDER.SOHNUM_0
INNER JOIN [SCHEMA].ITMMASTER
ON [SCHEMA].SORDERQ.ITMREF_0 = [SCHEMA].ITMMASTER.ITMREF_0
RIGHT OUTER JOIN [SCHEMA].SORDERP
ON [SCHEMA].SORDERQ.SOPLIN_0 = [SCHEMA].SORDERP.SOPLIN_0
AND [SCHEMA].SORDER.SOHNUM_0 = [SCHEMA].SORDERP.SOHNUM_0
WHERE ([SCHEMA].SORDERQ.CPY_0 = N'BZES')
AND (YEAR([SCHEMA].SORDERQ.ORDDAT_0) = YEAR(GETDATE()))
GROUP BY 'Semana' + Right('0'+STR(DATEPART(WEEK, [SCHEMA].SORDERQ.ORDDAT_0)),2)
推荐阅读
- mongodb - allowDiskUse 对 mongodb 中的 find() 方法有效吗?
- python - 为字典中的每个列表值组合创建一个字典
- latex - 一个表而不是三个单独的表中的多列
- linux - 忽略文件类型的两个目录之间的区别?
- python - 在networkx中计算传出和传入边时,带有'dict_keyiterator'的len throws没有len()
- javascript - 对对象数组中每个对象的键进行排序
- c# - C# 图表 - 难以绘制不同频率的时间序列
- java - 缩放图形绘图区域 Java Swing/awt
- python - 如何通过不同的函数PYTHON正确传递变量
- javascript - 标识符“normal_push”不是骆驼大小写