December 23, 2013 at 9:55 pm
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
December 23, 2013 at 10:35 pm
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/
December 23, 2013 at 11:15 pm
Wow, perfect. Thanks. Happy Holidays.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply