Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Complex Query using Self-Joins Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, December 23, 2013 9:55 PM
 SSC Rookie Group: General Forum Members Last Login: Monday, December 23, 2013 11:09 PM Points: 36, Visits: 5
 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.INPUTclose_total received_tag monetary_type_id------------- ------------- ----------------01.00 20131212 102.00 20131212 210.00 20131213 120.00 20131213 130.00 20131213 240.00 20131213 250.00 20131213 360.00 20131213 3OUTPUTreceived_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.0020131213 210.00 30.00 70.00 110.00 NOTESsum_close_total_all - monetary_type_id = ALLsum_close_total_1 - monetary_type_id = 1sum_close_total_2 - monetary_type_id = 2sum_close_total_3 - monetary_type_id = 3Thanks,Steve
Post #1525681
 Posted Monday, December 23, 2013 10:35 PM
 Say Hey Kid Group: General Forum Members Last Login: 2 days ago @ 7:24 PM Points: 693, Visits: 3,802
 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 col3FROM @t1GROUP BY tag_date` __________________________________________________________________________________________________________How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1525683
 Posted Monday, December 23, 2013 11:15 PM
 SSC Rookie Group: General Forum Members Last Login: Monday, December 23, 2013 11:09 PM Points: 36, Visits: 5
 Wow, perfect. Thanks. Happy Holidays.
Post #1525687

 Permissions