CODEWITHSUNDEEP
sql-serverpivot
user3115933
user3115933

Reputation: 4443

How to write an SQL Pivot Query for this scenario?

I am using SQL Server 2014 and I have the following query that runs fine:

SELECT  b.FOH_PMSCONFNUM,
    a.FOC_ACCOUNT, 
    a.FOC_PROPERTY, 
    a.FOC_TCODE, 
    a.FOC_NETAMOUNT


FROM P5FOLIOCHARGE a

LEFT JOIN P5FOLIOHEADER b ON a.FOC_ACCOUNT = b.FOH_ACCOUNT

where b.FOH_PMSCONFNUM = '1458' AND FOC_NETAMOUNT NOT LIKE '-%'

It gives me the following output:

FOH_PMSCONFNUM    FOC_ACCOUNT    FOC_PROPERTY   FOC_TCODE  FOC_NETAMOUNT
125                52             BMA            ROOMS       1,200
125                52             BMA            zBev          900
125                52             BMA            zTel          200
125                52             BMA            ROOMS       1,200
125                52             BMA            zSpa          500
125                52             BMA            zTel          100

I am having a tough time writing the pivot query so that my output turns out as follows:

FOH_PMSCONFNUM   FOC_ACCOUNT   FOC_ PROPERTY  ROOMS   zBev    zTel   zSpa
125                52            BMA          2,400   900     300    500

Also, while running this pivot query, there are 2 things I need to consider:

(1) I must keep this statement "AND FOC_NETAMOUNT NOT LIKE '-%'", so that the pivot does not sum negative figures that are present in the FOC_NETAMOUNT column. (2) For illustration purposes here, I have shown only a few items that exist in the FOC_TCODE column. In reality, I don't know how many items exists (may be around 30) and my aim is to output those items as column headers.

It is Note (2) above that is a hard nut to crack (for me at least!).

Upvotes: 2

Views: 118

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

You need to use Dynamic pivot

In Pivot source query make the negative values as zero so that it won't be used in SUM aggregate of pivot

DECLARE @sql  NVARCHAR(max),
        @cols VARCHAR(max)

SET @cols = (SELECT DISTINCT a.FOC_TCODE + ','
             FROM   P5FOLIOCHARGE a
                    LEFT JOIN P5FOLIOHEADER b
                           ON a.FOC_ACCOUNT = b.FOH_ACCOUNT
             WHERE  b.FOH_PMSCONFNUM = '1458'
                    AND FOC_NETAMOUNT NOT LIKE '-%'
             FOR xml path(''))

SELECT @cols = LEFT(@cols, Len(@cols) - 1)

SET @sql = 'SELECT *
        FROM   (SELECT b.FOH_PMSCONFNUM,
                       a.FOC_ACCOUNT,
                       a.FOC_PROPERTY,
                       a.FOC_TCODE,
                       CASE WHEN a.FOC_NETAMOUNT > 0 THEN a.FOC_NETAMOUNT ELSE 0 END AS FOC_NETAMOUNT
                FROM P5FOLIOCHARGE a
                     LEFT JOIN P5FOLIOHEADER b ON a.FOC_ACCOUNT = b.FOH_ACCOUNT
                where b.FOH_PMSCONFNUM = ''1458'' 
                AND FOC_NETAMOUNT NOT LIKE ''-%'')a
               PIVOT (Sum(FOC_NETAMOUNT)
                     FOR FOC_TCODE IN (' + @cols + ')) pv '

EXEC Sp_executesql @sql

Upvotes: 1

Related Questions