Need help in Pivot Table with Column Names to Rows.

• Rahuul

  Mr or Mrs. 500

  Points: 581

  More actions

  August 1, 2015 at 1:56 pm

  #398832

  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

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  August 1, 2015 at 7:07 pm

  #1816630

  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

• Rahuul

  Mr or Mrs. 500

  Points: 581

  More actions

  August 2, 2015 at 2:53 am

  #1816638

  Thanks a lot Jason.

  Saved my day!!

  On another note, could it be done using Pivot?

  Thanks again.

  Rahuul

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  August 2, 2015 at 7:06 am

  #1816646

  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.

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  August 2, 2015 at 11:45 am

  #1816684

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)