Merging two columns - 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

General SQL Server Forums

New to SQL Server Programming

Merging two columns

Author

Topic

luckycsc
Starting Member

7 Posts

Posted - 2009-02-11 : 19:23:30

Hello everyone, I am new to sql and I am trying to merge two columns into 1, like

ID ROLE ROLE_NAME

1 DEAN MEDICAL SCHOOL DEAN 1 CHAIRMAN BANK OF AMERICA
2 PRESIDENT SALIENT CORPORATION

After writing a Query this look like.....These columns should be merged into one.......such as

ID ROLE

1 DEAN:MEDICAL SCHOOL DEAN
1 CHAIRMAN:BANK OF AMERICA
2 PRESIDENT:SAILENT CORPORATION.

I am using a query
"select id, role + ':' + role_name as 'ROLE' from "TABLE NAME";

Its merging as "ROLE" but its giving all the values as zero's instead of the above view

Can anyone please help me out in this.....Thanks a lot

Jay

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-11 : 19:32:52

That's unusual. I created a test setup and it worked fine for me. Could you post a sample row that is coming back wrong (change the data of course to something meaningless). Maybe I'm not understanding your issue.

create table #temp(id int, role varchar(20),rolename varchar(20))

insert into #temp values(1,'TestRole','Testrolename')

select id, role + ':' + rolename as 'ROLE' from #temp

Mike
"oh, that monkey is going to pay"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-11 : 23:38:10

use coalesce or isnull when concateting the varchar fields
declare @tab table(ID int, ROLE varchar(32),ROLE_NAME varchar(32))
insert into @tab select 1, 'DEAN', 'MEDICAL SCHOOL DEAN'
union all select 1, 'CHAIRMAN', 'BANK OF AMERICA'
union all select 2, 'PRESIDENT', 'SALIENT CORPORATION'

select id, coalesce(role,'') +':'+coalesce(role_name,'') as role from @tab

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-11 : 23:45:57

use coalesce or isnull when concateting the varchar fields
declare @tab table(ID int, ROLE varchar(32),ROLE_NAME varchar(32))
insert into @tab select 1, 'DEAN', 'MEDICAL SCHOOL DEAN'
union all select 1, 'CHAIRMAN', 'BANK OF AMERICA'
union all select 2, 'PRESIDENT', 'SALIENT CORPORATION'
select id, coalesce(role+':','') +coalesce(role_name,'') as role from @tab

Jai Krishna

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 07:13:09

quote:
Originally posted by bklr

use coalesce or isnull when concateting the varchar fields
declare @tab table(ID int, ROLE varchar(32),ROLE_NAME varchar(32))
insert into @tab select 1, 'DEAN', 'MEDICAL SCHOOL DEAN'
union all select 1, 'CHAIRMAN', 'BANK OF AMERICA'
union all select 2, 'PRESIDENT', 'SALIENT CORPORATION'

select id, coalesce(role,'') +':'+coalesce(role_name,'') as role from @tab

So Jai Krishna and Bklr are same person.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-12 : 07:26:58

no yaar why u have got that doubt

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-12 : 07:52:01

Just I modified the query he has posted
select id, coalesce(role+':','') +coalesce(role_name,'') as role from @tab

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 09:33:05

quote:
Originally posted by bklr

use coalesce or isnull when concateting the varchar fields
declare @tab table(ID int, ROLE varchar(32),ROLE_NAME varchar(32))
insert into @tab select 1, 'DEAN', 'MEDICAL SCHOOL DEAN'
union all select 1, 'CHAIRMAN', 'BANK OF AMERICA'
union all select 2, 'PRESIDENT', 'SALIENT CORPORATION'

select id, coalesce(role,'') +':'+coalesce(role_name,'') as role from @tab

NULL wont cause result to be 0 as op told in first post.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 09:40:20

[code]DECLARE @Sample TABLE
(
ID INT,
[ROLE] VARCHAR(20),
[ROLE_NAME] VARCHAR(20)
)

INSERT @Sample
SELECT 1, 'DEAN', 'MEDICAL SCHOOL DEAN' UNION ALL
SELECT 1, 'CHAIRMAN', 'BANK OF AMERICA' UNION ALL
SELECT 2, 'PRESIDENT', 'SALIENT CORPORATION'

SELECT *
FROM @Sample

SELECT ID,
[ROLE] + ':' + [ROLE_NAME] AS Peso
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

luckycsc
Starting Member

7 Posts

Posted - 2009-02-12 : 10:45:58

Thanks a lot to you all
That was a great help

Actually there was a CONCAT command, which worked out fine....

It was like...

CREATE VIEW ROLE_DESCRIPTION AS SELECT id, CONCAT( ROLE, ':', ROLE_NAME ) AS ROLE FROM DS_ROLES

It merges to

id ROLE
1 DEAN:Medical School Dean
1 Chairman: Bank of America
2 President:Sailent Corporation

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 10:50:04

Another one missed this is a MICROSOFT SQL Server forum.
But on the other hand we should be proud, that ORACLE people rather come to us for help than going to www.dbforums.com

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources