August 1, 2015 at 1:56 pm
Hi,
Need help in Pivot function. I have a table with following rows.
FY REVCODE Jul Jun
2015 BNQ 1054839 2000000
2015 FNB 89032 1000000
2015 RS 1067299 3000000
I am looking to convert it to
Month BNQ FNB RS
JUL 1054839 89032 1067299
JUN 2000000 1000000 3000000
I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun
SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS]
FROM
(SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL
FROM RM_USERBUDGETTBL
WHERE USERNAME='rahul' AND FY=2015
GROUP BY REVENUECODE, USERNAME
) AS SourceTable
PIVOT
(SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable
Results:
MONTHBNQ FNB RS
Jul11054839 89032 1067299
Thanks in advance..
-Rahuul
August 1, 2015 at 7:07 pm
This should give you what you're looking for...
-- Test data --
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
FY INT,
REVCODE VARCHAR(3),
Jul INT,
Jun INT
);
INSERT #temp (FY,REVCODE,Jul,Jun) VALUES
(2015,'BNQ',1054839,2000000),
(2015,'FNB',89032,1000000),
(2015,'RS',1067299,3000000);
-- The solution --
SELECT
t.FY,
x.[Month],
SUM(CASE WHEN t.REVCODE = 'BNQ' THEN x.Amount ELSE 0 END) AS BNQ,
SUM(CASE WHEN t.REVCODE = 'FNB' THEN x.Amount ELSE 0 END) AS FNB,
SUM(CASE WHEN t.REVCODE = 'RS' THEN x.Amount ELSE 0 END) AS RS
FROM
#temp t
CROSS APPLY (VALUES ('Jul', t.Jul), ('Jun', t.Jun)) x ([Month],Amount)
GROUP BY
t.FY,
x.[Month]
The results...
FYMonthBNQFNBRS
2015Jul1054839890321067299
2015Jun200000010000003000000
August 2, 2015 at 2:53 am
Thanks a lot Jason.
Saved my day!!
On another note, could it be done using Pivot?
Thanks again.
Rahuul
August 2, 2015 at 7:06 am
Rahuul (8/2/2015)
Thanks a lot Jason.Saved my day!!
You're welcome! Glad to help. 🙂
On another note, could it be done using Pivot?
Yes. There are several ways to write this query.
August 2, 2015 at 11:45 am
Rahuul (8/2/2015)
Thanks a lot Jason.Saved my day!!
On another note, could it be done using Pivot?
Thanks again.
Rahuul
Using PIVOT for this can make things slower but, for more information, please see the following articles.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply