Warm tip: This article is reproduced from stackoverflow.com, please click
tsql

Pivot this different way

发布于 2020-03-27 15:41:10

i am wondering if i could pivot this in a different way, for now this is what i got :

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

Which returns

Output

This is the output i get where 'Semana1' means Week1

Could somehow order it to get something like this :

          BZ        TDZ

Week1    93150.94   425902.09
Week2
Week3

This is mainly because i am ordering this by week and there are a lot of weeks in a year. The way i have it the output is very large (horizontally)

Thanks !

Questioner
Antonio
Viewed
28
Søren Kongstad 2020-01-31 16:24

You are basically doing it in you inner select.

I have cleaned it up a bit, and just divided the sum into 2 different sums, one for BZ and one for TDZ

And don't use {fn week(...}, just use

datepart(week,SCHEMA.SORDERQ.ORDDAT_0) 

Or if you are in the EU use

datepart(ISO_WEEK,SCHEMA.SORDERQ.ORDDAT_0) 

This is the full query

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)