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

 Multiple Rows in One Resultset Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, October 27, 2012 7:49 AM
 Forum Newbie Group: General Forum Members Last Login: Saturday, July 11, 2015 4:44 PM Points: 5, Visits: 15
 Hi All,Let's say I have a table like the following:row1 row2 row3 row40 6 0 00 0 30 04 0 0 00 0 0 18but I want to return a resultset like:row1 row2 row3 row44 6 30 18Does anyone have any ideas as to how I would do that with T-SQL?Thanks,C
Post #1377949
 Posted Saturday, October 27, 2012 7:52 AM
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 29, 2016 5:30 AM Points: 424, Visits: 1,271
 -- Test data:declare @a as table(row1 int,row2 int, row3 int,row4 int);insert @a values (0,6,0,0);insert @a values (0,0,30,0);insert @a values (4,0,0,0);insert @a values (0,0,0,18);--select * from @a;-- Example solution:select max(case when row1 <> 0 then row1 end) as Row1, max(case when row2 <> 0 then row2 end) as Row2, max(case when row3 <> 0 then row3 end) as Row3, max(case when row4 <> 0 then row4 end) as Row4from @a
Post #1377950
 Posted Saturday, October 27, 2012 9:12 AM
 Forum Newbie Group: General Forum Members Last Login: Saturday, July 11, 2015 4:44 PM Points: 5, Visits: 15
 Laurie,Thanks so much. I think that is really gonna help me.C~
Post #1377955
 Posted Saturday, October 27, 2012 9:44 AM
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 29, 2016 5:30 AM Points: 424, Visits: 1,271
 Noooo problem!
Post #1377959
 Posted Monday, October 29, 2012 2:53 PM
 Valued Member Group: General Forum Members Last Login: Tuesday, August 9, 2016 3:25 PM Points: 54, Visits: 99
 Here is my two cents.You don't need a Case Statement. Just simple One Line Query would work fine.declare @MyTable as table(Column1 int,Column2 int, Column3 int,column4 int);insert @MyTable values (0,6,0,0);insert @MyTable values (0,0,30,0);insert @MyTable values (4,0,0,0);insert @MyTable values (0,0,0,18);Select Col1 = Max(Column1), Col2 = Max(Column2), Col3 = Max(Column3), Col4 = Max(Column4) FROM @MyTable
Post #1378479
 Posted Tuesday, October 30, 2012 6:16 PM
 Forum Newbie Group: General Forum Members Last Login: Saturday, July 11, 2015 4:44 PM Points: 5, Visits: 15
Post #1379110
 Posted Thursday, November 1, 2012 3:01 PM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, March 18, 2015 10:23 PM Points: 266, Visits: 162
 clay.calvin,The SUM() function is another option that may be applicable. Compare the MAX() function to the SUM() function (note I have changed the VALUES in the fourth insert to demonstrate the difference):`DECLARE @MyTable AS TABLE(Column1 INT,Column2 INT, Column3 INT,column4 INT);INSERT @MyTable VALUES (0,6,0,0);INSERT @MyTable VALUES (0,0,30,0);INSERT @MyTable VALUES (4,0,0,0);INSERT @MyTable VALUES (1,2,3,18);SELECT Col1 = MAX(Column1), Col2 = MAX(Column2), Col3 = MAX(Column3), Col4 = MAX(Column4) FROM @MyTableSELECT Col1 = SUM(Column1), Col2 = SUM(Column2), Col3 = SUM(Column3), Col4 = SUM(Column4) FROM @MyTableoutput from first statement: Col1 Col2 Col3 Col4----------- ----------- ----------- ----------- 4 6 30 18output from second statement: Col1 Col2 Col3 Col4----------- ----------- ----------- ----------- 5 8 33 18`The aggregate function you will choose depends on what you want to accomplish.-gjr
Post #1380046
 Posted Thursday, November 1, 2012 8:08 PM
 Forum Newbie Group: General Forum Members Last Login: Saturday, July 11, 2015 4:44 PM Points: 5, Visits: 15
 Thanks everyone,Laurie's suggestion worked perfectly. I'm on to the next project. I did not mean to offend anyone or make things hard for people to help me. I'll remember the tip for the next post.C~
Post #1380110

 Permissions