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!).