pivot

• maxgiose

  Newbie

  Points: 1

  More actions

  May 15, 2008 at 5:17 pm

  #186645

  Hi all,

  I need to change the following selection:

  acct_nbr txn_date txn_amt

  123 2008-01-14 50.00

  123 2008-02-24 120.00

  123 2008-02-21 70.00

  124 2008-01-30 20.00

  124 2008-02-03 110.00

  125 2008-03-19 70.00

  into this:

  acct_nbr txn_date_1 txn_amt_1 txn_date_2 txn_amt_2 txn_date_3 txn_amt_3

  123 2008-01-14 50.00 2008-02-24 120.00 2008-02-21 70.00

  124 2008-01-30 20.00 2008-02-03 110.00 NULL NULL

  125 2008-03-19 70.00 NULL NULL NULL NULL

  The transactions may vary from 1 to 3 maximum.

  Can you please help me?

  Thank you

  Regards

  Mark

• Jeff Moden

  SSC Guru

  Points: 1004706

  More actions

  May 15, 2008 at 6:50 pm

  #816219

  Welcome aboard, Mark,

  Hey... since you're a "newbie", take a look at the URL in my signature line. It'll help you and folks like me a lot in the future.

  On to your problem...

  Take a look at this URL in Books Online to see how this works... I use this method instead of PIVOT all the time....

  http://msdn.microsoft.com/en-us/library/aa172756.aspx

  Here's a full working example...

  --===== Create and populate a test table

  -- This is what I like to see in forum posts...

  -- Saves me lot's of time, gets you a better answer, quicker.

  DECLARE @TestTable TABLE

  (

  acct_nbr INT,

  txn_date DATETIME,

  txn_amt DECIMAL(9,2))

  INSERT INTO @TestTable

  (acct_nbr,txn_date,txn_amt)

  SELECT '123','2008-01-14','50.00' UNION ALL

  SELECT '123','2008-02-24','120.00' UNION ALL

  SELECT '123','2008-02-21','70.00' UNION ALL

  SELECT '124','2008-01-30','20.00' UNION ALL

  SELECT '124','2008-02-03','110.00' UNION ALL

  SELECT '125','2008-03-19','70.00'

  --===== Here's the answer to the question you asked.

  -- Execute the SELECT inside the CTE by itself to see what it does.

  ;WITH

  cteEAV AS

  (

  SELECT DENSE_RANK() OVER (PARTITION BY acct_nbr ORDER BY acct_nbr,txn_date) AS Rank,

  acct_nbr,txn_date,txn_amt

  FROM @TestTable

  )

  --===== This does the cross-tab on the data from the CTE above

  SELECT acct_nbr,

  MAX(CASE WHEN Rank = 1 THEN txn_date END) AS txn_date1,

  MAX(CASE WHEN Rank = 1 THEN txn_amt END) AS txn_amt1,

  MAX(CASE WHEN Rank = 2 THEN txn_date END) AS txn_date2,

  MAX(CASE WHEN Rank = 2 THEN txn_amt END) AS txn_amt2,

  MAX(CASE WHEN Rank = 3 THEN txn_date END) AS txn_date3,

  MAX(CASE WHEN Rank = 3 THEN txn_amt END) AS txn_amt3,

  SUM(txn_amt) AS Total

  FROM cteEAV

  GROUP BY acct_nbr

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