Combine two row into one row. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Combine two row into one row.

Author

Topic

kingstonr
Starting Member

3 Posts

Posted - 2010-10-15 : 10:10:07

I want to combine two rows into one.
ex:-
my table is like below.

ID Col1 col2 col3 col4
24 null 3 null null
4 null null 4 5

I need like below.

col1 col2 col3 col4
null 3 4 5

Thanks for the help.

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-10-15 : 10:15:14

Consider that for Col2, ID4 may have a not null value. In this case, where you have two values to deal with, which one do you want? You can choose the min, max, avg, sum, etc... Once you know this, select the relevant aggregate function and use it in your query to "combine two rows into one."

[url]http://msdn.microsoft.com/en-us/library/ms173454.aspx[/url]

kingstonr
Starting Member

3 Posts

Posted - 2010-10-15 : 10:27:08

Hi,
I think since i have null values group by is not working.

ID Col1 col2 col3 col4
24 null  3  null  null
4 null  null  4    5

I need like below.

col1 col2 col3 col4
null 3     4      5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 03:21:46

i cant understand on what basis you group these two rows together. i cant see anything common between them. or is it like you're trying to group all records in table to 1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-10-18 : 07:37:09

select id,max(isnull(col1,0)) col1,max(isnull(col2,0)) col2,,max(isnull(col3,0)) col3 from table1

theMC
Starting Member

2 Posts

Posted - 2010-10-18 : 12:50:49

Select Col1, Sum(Col2), Sum(Col3) , Sum(Col4)
from (
Select Isnull(Col1,0)Col1 , Isnull(Col2,0)Col2, Isnull(Col3,0)Col3, Isnull(Col4,0) Col4 from <Your Table Name>
) M
Group By M.Col1

Mayukh Chowdhury
Email: mayukhmail@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 13:29:47

quote:
Originally posted by kunal.mehta

select id,max(isnull(col1,0)) col1,max(isnull(col2,0)) col2,,max(isnull(col3,0)) col3 from table1

this wont even compile unless you add a group by id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 13:34:37

quote:
Originally posted by kingstonr

I want to combine two rows into one.
ex:-
my table is like below.

ID Col1 col2 col3 col4
24 null 3 null null
4 null null 4 5

I need like below.

col1 col2 col3 col4
null 3 4 5

Thanks for the help.

This requirement is so ridiculous for me.
How do you define your "two" rows?
Are you always so sure that there will always be "two" rows ?
What happens if number of rows are odd?
What happens if two consecutive rows are not null at all?How are you going to combine them?

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources