two columns concatination add spaces to first colu

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 2005 Forums

Transact-SQL (2005)

two columns concatination add spaces to first colu

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2009-10-20 : 11:53:34

select eName,Age from empl

Ename Age
------- ----
Jhones 35
PATRICK 21
MathewJunior 29
Dan 41

when i use below query

select eName || ' ' || Age from empl

Jhones 35
PATRICK 21
MathewJunior 29
Dan 41

is there any way that i can get as below

Jhones 35
PATRICK 21
MathewJunior 29
Dan 41

Please Note: here in the above output the important thing is what ever the string length it has to be aligned as max length string + 1 empty character then age
is it possible

please help me sir

Thanks in adavnce
dhani

dhani
Posting Yak Master

132 Posts

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

Please Any ideas
......

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-20 : 12:14:11

as below looks exactly as above...can you clarify please...so that we can help

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

dhani
Posting Yak Master

132 Posts

Posted - 2009-10-20 : 12:18:49

i am very sorry
for confusing all of you,

let me explain you total....,,

create table emplo (eName varchar(20),age int);

insert into emplo values ('AAA',78);
insert into emplo values ('BBBBBB',45);
insert into emplo values ('C',99);
insert into emplo values ('DDDDDDDDD',12);
insert into emplo values ('EEEEE',56);
insert into emplo values ('FFFF',60);
insert into emplo values ('GGGGGGGGGGGGGGG',50);
insert into emplo values ('HH',14);

when i execute query
select sname + cast(age as varchar) from emplo
the result came in a zig zag position (when age value starts after ename because ename is having different length if values )
here what i am trying is start age at a particular (i through max length + 1 space) position

for ex AAA is 3 character length string here in this table max length is 15 value GGGG\s
so AAA then add (15 - 3) 12 spaces then add age value so next BBBBBB has 6 characters in length so just add 9 spaces then display age after that
so finally i will get a eName and age at a nice location (not in a zig zag format)

hope this helps a little
if not let me know i will give some more examples

thanks to you, please get out this problem
dhani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 12:59:01

seems like select str(sname + cast(age as varchar),15) from emplo

dhani
Posting Yak Master

132 Posts

Posted - 2009-10-20 : 13:20:57

Hello Visakh, Thanks for your Help,

but while i am trying to execute the query i am getting below error, i am using sql server 2005

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

quote:
Originally posted by visakh16

seems like select str(sname + cast(age as varchar),15) from emplo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 13:42:17

ah...that was a varchar field. then use this

select sname + str(age,15- len(sname)) from emplo

dhani
Posting Yak Master

132 Posts

Posted - 2009-10-20 : 14:15:20

Thanks Visakh,
Half of the way
when i select result to text option in (Sql Server Management Studeio --> Query menu--> Results to --> Result to Text) is working as i expected,
but when i see results in (Sql Server Management Studeio --> Query menu--> Results to --> Result to Grid) is not working

is there any way (the reason is i am using this in one reporting tool which is Business Objects but backend is Sql server, so i have to place two values in one cell which is in a nice way unlike zig zag way

please there is nothing that i can set @ Business Objects Level, only i have to do @ sql server level

Thansk inadvance
dhani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 14:19:58

select sname + replicate(' ',15-len(sname)) + cast(age as varchar) from emplo

dhani
Posting Yak Master

132 Posts

Posted - 2009-10-20 : 15:12:37

Really Thanks to you Visakh,

i chaged the font type to LUCIDA it is owrking fine with you last post
and also Thanks to all whome ever contributed their ideas in this post

Thanks to alll

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 15:16:15

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources