Splitting rows into columns

• Shabu George

  SSC Enthusiast

  Points: 121

  More actions

  April 8, 2006 at 10:32 am

  #114265

  Dear Friends,

  I have a table which has  2 types of records A and B.

  How do i split these rows into columns column A and column A

  TxnNo    Amount   Type

  100        1500       R

  101        2000       C

  102        2000       C

  Result

  Txnno     Sum(Amount type R)          sum(Amount Type C)

  100         1500                                        2000+2000=4000

   

   

  Thanks

  S.George

   

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  April 8, 2006 at 11:29 am

  #631625

  Unless your data actually looks more like the following, the code is going to be a wee bit more complicated than what I have below...

  TxnNo    Amount   Type

  100        1500       R

  100        2000       C

  100        2000       C

  If the data does look like the above, then this would do...

  SELECT TxnNo,
         SUM(CASE WHEN Type = 'R' THEN Amount ELSE 0 END) AS [Sum(Amount Type R)],
         SUM(CASE WHEN Type = 'C' THEN Amount ELSE 0 END) AS [Sum(Amount Type C)]
    FROM yourtable
   GROUP BY TxnNo

  If the data actually looks like what you posted, you need to provide some extra info in how to distiguish what goes with what.  For example, will the "C" records always follow a given "R" record in numerical sequence by TxnNo?  Will there always be just 1 "R" record for each group of "C" records?  Will there ever be any interceding records?  If so, what criteria is there to filter them out?

  You get the idea... we need a bit more info on what you really want to do and perhaps a bit more data so we can test our answer.

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