Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple Rows in One Resultset Expand / Collapse
Author
Message
Posted Saturday, October 27, 2012 7:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 12, 2015 10:06 PM
Points: 5, Visits: 13
Hi All,

Let's say I have a table like the following:

row1 row2 row3 row4
0 6 0 0
0 0 30 0
4 0 0 0
0 0 0 18

but I want to return a resultset like:

row1 row2 row3 row4
4 6 30 18

Does 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


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 23, 2015 2:52 AM
Points: 393, Visits: 1,223

-- 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
Post #1377950
Posted Saturday, October 27, 2012 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 12, 2015 10:06 PM
Points: 5, Visits: 13
Laurie,

Thanks so much. I think that is really gonna help me.


C
~
Post #1377955
Posted Saturday, October 27, 2012 9:44 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 23, 2015 2:52 AM
Points: 393, Visits: 1,223
Noooo problem!
Post #1377959
Posted Monday, October 29, 2012 2:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 2:22 PM
Points: 54, Visits: 86
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 12, 2015 10:06 PM
Points: 5, Visits: 13
T,

Thanks for your reply.

C
~
Post #1379110
Posted Thursday, November 1, 2012 3:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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 @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
Post #1380046
Posted Thursday, November 1, 2012 8:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 12, 2015 10:06 PM
Points: 5, Visits: 13
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse