Complex Query using Self-Joins

• Steve DiDomenico-197994

  SSChasing Mays

  Points: 638

  More actions

  December 23, 2013 at 9:55 pm

  #100172

  As an example, is it possible write SQL to take these 8 rows and return the following 2 rows? My self-joins are too simple and I'm getting a cartesian effect and can't figure this out.

  INPUT

  close_total received_tag monetary_type_id

  ------------- ------------- ----------------

  01.00 20131212 1

  02.00 20131212 2

  10.00 20131213 1

  20.00 20131213 1

  30.00 20131213 2

  40.00 20131213 2

  50.00 20131213 3

  60.00 20131213 3

  OUTPUT

  received_tag sum_close_total_all sum_close_total_1 sum_close_total_2 sum_close_total_3

  --------------------------------------------------------------------------------------------------

  20131212 3.00 1.00 2.00 0.00

  20131213 210.00 30.00 70.00 110.00

  NOTES

  sum_close_total_all - monetary_type_id = ALL

  sum_close_total_1 - monetary_type_id = 1

  sum_close_total_2 - monetary_type_id = 2

  sum_close_total_3 - monetary_type_id = 3

  Thanks,

  Steve

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  December 23, 2013 at 10:35 pm

  #1675725

  Try this:

  SELECT tag_date

  , SUM(close_total)

  , ISNULL(SUM(CASE id WHEN 1 THEN close_total ELSE 0 END), 0) AS col1

  , ISNULL(SUM(CASE id WHEN 2 THEN close_total ELSE 0 END), 0) AS col2

  , ISNULL(SUM(CASE id WHEN 3 THEN close_total ELSE 0 END), 0) AS col3

  FROM @t1

  GROUP BY tag_date

  __________________________________________________________________________________________________________
  How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Steve DiDomenico-197994

  SSChasing Mays

  Points: 638

  More actions

  December 23, 2013 at 11:15 pm

  #1675727

  Wow, perfect. Thanks. Happy Holidays.