October 27, 2012 at 7:49 am
Hi All,
Let's say I have a table like the following:
row1row2row3row4
0600
00300
4000
00018
but I want to return a resultset like:
row1row2row3row4
463018
Does anyone have any ideas as to how I would do that with T-SQL?
Thanks,
C
October 27, 2012 at 7:52 am
-- 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 Row4
from @a
October 27, 2012 at 9:12 am
Laurie,
Thanks so much. I think that is really gonna help me.
C
~
October 27, 2012 at 9:44 am
Noooo problem!
October 29, 2012 at 2:53 pm
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
October 30, 2012 at 6:16 pm
T,
Thanks for your reply.
C
~
November 1, 2012 at 3:01 pm
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 @MyTable
SELECT Col1 = SUM(Column1), Col2 = SUM(Column2), Col3 = SUM(Column3), Col4 = SUM(Column4)
FROM @MyTable
output from first statement:
Col1 Col2 Col3 Col4
----------- ----------- ----------- -----------
4 6 30 18
output 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
November 1, 2012 at 8:08 pm
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
~
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply