question

Bins avatar image
Bins asked

How to get the result using pivot command in sql

I have a query that displays the result in the attached format (Refer table ![Current Format)][1]. I need to write a query using **PIVOT** command to get a result in the attached format (Refer ![alt text][2] r). Please help to get the desired result. Below is the sql query: SELECT tbl.cli_code , tbl.Cli_Desc , tbl.Name , tbl.SalesAmount , tbl.CostAmount , ROUND(CASE WHEN ISNULL(tbl.SalesAmount, 0) = 0 THEN 0 ELSE ( tbl.SalesAmount - tbl.CostAmount ) END, 2) profit FROM ( SELECT i.cli_code , c.Cli_Desc , fmg.Name , SalesAmount = ISNULL(SUM(id.Det_Total * ( 1 - ISNULL(Inv_Discount, 0) / 100 ) * ( CASE WHEN '1' = i.Cur_Code THEN 1 ELSE dbo.GetcurrencyRate(i.cur_code, '1', CONVERT(NVARCHAR(20), i.inv_date, 101)) END ) * ( CASE WHEN ISNULL(inv_return, 0) = 1 THEN -1 ELSE 1 END )), 0) , CostAmount = ISNULL(SUM(id.det_costf * id.det_qty * ( CASE WHEN ISNULL(inv_return, 0) = 1 THEN -1 ELSE 1 END )), 0) FROM AccInvoice i INNER JOIN FTCLIENT c ON c.Cli_Code = i.Cli_Code AND i.sys_stcode <> '4' AND Inv_Date BETWEEN '01/01/2016' AND '01/02/2016' INNER JOIN AccInv_Detail id ON i.inv_nbr = id.inv_nbr INNER JOIN ftproduct prd ON id.prd_code = prd.prd_code INNER JOIN ftFamily fm ON prd.fam_code = fm.fam_code INNER JOIN FTFamStatGroup fmg ON fmg.fam_code = fm.fam_code GROUP BY i.cli_code , fmg.Name , c.Cli_Desc HAVING ISNULL(SUM(id.Det_Total * ( 1 - ISNULL(Inv_Discount, 0) / 100 ) * dbo.GetcurrencyRate(i.cur_code, 1, CONVERT(NVARCHAR(20), i.inv_date, 101)) * ( CASE WHEN ISNULL(inv_return, 0) = 1 THEN -1 ELSE 1 END )), 0) <> 0 ) tbl [1]: /storage/temp/3637-current-format.png [2]: /storage/temp/3638-required-result.png
query-resultsresult
current-format.png (14.8 KiB)
required-result.png (14.5 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Bins avatar image
Bins answered
I got the result.. I used both the UNPIVOT and the PIVOT functions to get the result.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.