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

 How to collapse rows but take a specific value? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, October 7, 2013 9:57 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, August 21, 2014 9:20 AM Points: 16, Visits: 47
 Hi,I'm struggling as to how to collapse rows in a view based on different values in a column, but take one specific value. Here's the sample data with four columns:AccountNumber ------MaturityDate ------ResetFreq ------ Amount123 ------ 20140331 ------ 1D ------- 100.00123 ------ 20140331 ------ 1M ------- 2400.00123 ------ 20140331 ------ '' ------- 8700.00123 ------ 20140331 ------ 1Y ------- -99.00123 ------ 20140331 ------ 1M ------- 299.00I want to collapse these four rows into one row:AccountNumber ------MaturityDate ------ResetFreq ------ Sum(Amount)123 ------ 20140331 ------ 1Y ------- 11400.00For ResetFrequency, I need to take the highest value among the distinct values listed for that account.The hierarchy is: '' (blank) < 1D (1 day) < 1M (1 month) < 3M (3 months) < 1Y (1 year) < 3Y < 5Y < 10YSo, in this case, the distinct values are: '', 1D, 1M, 1Y -- so I take 1Y as it is the highest.Any help will be appreciated.Amit
Post #1502235
 Posted Monday, October 7, 2013 10:28 AM
 SSChasing Mays Group: General Forum Members Last Login: 2 days ago @ 11:56 AM Points: 608, Visits: 974
 Here you go: `create table ResetData (accountNumber int, MaturityDate datetime, ResetFreq char(3), Amount decimal(6,2))insert into ResetData values (123 , '20140331' , '1D' ,100.00),(123 , '20140331' , '1M' ,2400.00),(123 , '20140331' , '' ,8700.00),(123 , '20140331' , '1Y' ,-99.00),(123 , '20140331' , '1M' ,299.00) select AccountNumber , MaturityDate , max( case when ResetFreq = '' then 1 when ResetFreq = '1D' then 2 when ResetFreq = '1M' then 3 when ResetFreq = '3M' then 4 when ResetFreq = '1Y' then 5 when ResetFreq = '3Y' then 6 when ResetFreq = '5Y' then 7 when ResetFreq = '10Y' then 8 end) , sum(Amount)from ResetDatagroup by AccountNumber, MaturityDate`For the ResetFreq you will get a number that you can case back to the naming you want i.e. case when 5 then '1Y' Microsoft Certified Master - SQL Server 2008Follow me on twitter: @keith_tateForum Etiquette: How to post data/code on a forum to get the best help
Post #1502255
 Posted Monday, October 7, 2013 10:29 AM
 SSChasing Mays Group: General Forum Members Last Login: 2 days ago @ 11:56 AM Points: 608, Visits: 974
 Next time you post a question like yours please try to include a sample create table statement along with some test data like my post back to you. This will help us answer your question quicker and makes it easier too. Microsoft Certified Master - SQL Server 2008Follow me on twitter: @keith_tateForum Etiquette: How to post data/code on a forum to get the best help
Post #1502256
 Posted Monday, October 7, 2013 11:37 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, August 21, 2014 9:20 AM Points: 16, Visits: 47
 Keith,Thanks for your response and suggestion (duly noted).Your solution has given me the idea. I need to tinker with a little bit - add a new table with Resetfrequency values and a column with corresponding hierarchy value - and then take the Resetfrequency value of the max(hierarchy value). A lookup table will take care of that.Thanks again.Amit
Post #1502283
 Posted Monday, October 7, 2013 6:44 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 Certainly no way around the CASE but I might do it this way so as to only have to CASE it once.`WITH SampleData (accountNumber, MaturityDate, ResetFreq, Amount) AS( SELECT 123 , '20140331' , '1D' ,100.00 UNION ALL SELECT 123 , '20140331' , '1M' ,2400.00 UNION ALL SELECT 123 , '20140331' , '' ,8700.00 UNION ALL SELECT 123 , '20140331' , '1Y' ,-99.00 UNION ALL SELECT 123 , '20140331' , '1M' ,299.00)SELECT accountNumber, MaturityDate ,ResetFreq=MAX(CASE rn WHEN 1 THEN ResetFreq END) ,Amount=SUM(Amount)FROM( SELECT * ,rn=ROW_NUMBER() OVER (PARTITION BY accountNumber, MaturityDate ORDER BY CASE ResetFreq WHEN '' THEN 1 WHEN '1D' THEN 2 WHEN '1M' THEN 3 WHEN '3M' THEN 4 WHEN '1Y' THEN 5 WHEN '3Y' THEN 6 WHEN '5Y' THEN 7 WHEN '10Y' THEN 8 END DESC) FROM SampleData) a GROUP BY accountNumber, MaturityDate;` My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1502439

 Permissions