Merge two columns together - 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)

Merge two columns together

Author

Topic

subt13
Starting Member

11 Posts

Posted - 2010-01-07 : 17:16:59

I have a result set in the form of:
col1 col2 col3
1 null null
2 null null
null 10 null
null 20 null
null null 30
null null 40

Any ideas on how to update this result to be:
col1 col2 col3
1 10 30
2 20 40

Thanks!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 17:25:12

Is this the result of a SELECT statement? If yes, can you show us that statement.

subt13
Starting Member

11 Posts

Posted - 2010-01-07 : 17:26:46

This is just a dump of the table (select * from table)

jcampbell
Starting Member

9 Posts

Posted - 2010-01-07 : 17:32:29

how do you know that 1 in col1 and 10 in col2 and 30 in col3 belong together?
Is there another field on all the rows that can identify a row of data?

subt13
Starting Member

11 Posts

Posted - 2010-01-07 : 17:35:08

quote:
Originally posted by jcampbell

how do you know that 1 in col1 and 10 in col2 and 30 in col3 belong together?
Is there another field on all the rows that can identify a row of data?

Sorry for the confusion. Yes.
Each pair of rows in this exmaple has a "group" identifier.
For example:
Col4 would be:
1
1
2
2
3
3

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 17:38:56

That doesn't help...Did you mean col4 would be
1
2
1
2
1
2

subt13
Starting Member

11 Posts

Posted - 2010-01-07 : 17:41:43

quote:
Originally posted by vijayisonly

That doesn't help...Did you mean col4 would be
1
2
1
2
1
2

No, I didn't.

subt13
Starting Member

11 Posts

Posted - 2010-01-07 : 17:46:06

If this is impossible to do without one column (or group of columns) that is/are unique for each row. Then I can add that to the table.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 17:56:43

I'm not sure..but this is what you may need...

declare @t table (col1 int, col2 int, col3 int, col4 int)
insert @t
select 1, null, null,1
union all select 2, null, null,2
union all select null, 10, null,1
union all select null, 20, null,2
union all select null, null, 30,1
union all select null, null,40,2

select col4,max(col1),max(col2),max(col3) from @t
group by col4

subt13
Starting Member

11 Posts

Posted - 2010-01-07 : 18:05:01

[quote]Originally posted by vijayisonly

I'm not sure..but this is what you may need...

declare @t table (col1 int, col2 int, col3 int, col4 int)
insert @t
select 1, null, null,1
union all select 2, null, null,2
union all select null, 10, null,1
union all select null, 20, null,2
union all select null, null, 30,1
union all select null, null,40,2

select col4,max(col1),max(col2),max(col3) from @t
group by col4

Perfect! Hard to believe it was that simple.

Thanks so much.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 23:19:50

Np. You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources