aggregate data from 2 columns in to 1

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

General SQL Server Forums

New to SQL Server Programming

aggregate data from 2 columns in to 1

Author

Topic

miguel_butler
Starting Member

2 Posts

Posted - 2010-02-28 : 13:18:41

Hi,

I'd like to use a view in MS SQL to take 2 columns of data in to 1.
I don't mean joining where there are ID matches I just want to join the 2nd column to the results of the first.

this isn't the data but it explains the issue.
e.g
columns
nameId, name1, name2
1 Bill Brian
2 Bob Ian
3 Dan Steve

into a view like this.
newID, nameID, name
1 1 Bill
2 1 Brian
3 2 Bob
4 2 Ian
5 3 Dan
6 3 Steve

It seems so easy to me ... and yet my mind is a blank on how to solve this. I'd appreciate advice from a SQL master who has not gone quite so brain dead as I appear to have.

Thanks

M

Sachin.Nand

2937 Posts

Posted - 2010-02-28 : 13:37:27

If u are using SQL 2005


select ROW_NUMBER()over(order by nameid)as newid,* from
(
select nameid,name1 from yourtable
union all
select  nameid,name2 from @yourtable
)t(nameid,name)
order by nameid

PBUH

miguel_butler
Starting Member

2 Posts

Posted - 2010-02-28 : 16:05:46

BRILLIANT - THANKS.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 02:26:53

quote:

Originally posted by Idera

If u are using SQL 2005


select ROW_NUMBER()over(order by nameid)as newid,* from
(
select nameid,name1 from yourtable
union all
select  nameid,name2 from @yourtable
)t(nameid,name)
order by nameid

PBUH

Hi, you used external column aliasing! You are a good person.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139850

Sachin.Nand

2937 Posts

Posted - 2010-03-01 : 02:40:55

quote:
Originally posted by ms65g

quote:
Originally posted by Idera

If u are using SQL 2005

select ROW_NUMBER()over(order by nameid)as newid,* from
(
select nameid,name1 from yourtable
union all
select nameid,name2 from @yourtable
)t(nameid,name)
order by nameid

PBUH

Hi, you used external column aliasing! You are a good person.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139850

Well I dont know whether I am good or not.But I like good ideas like the column aliasing you gave me.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources