Repeating Values problem - 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

Repeating Values problem

Author

Topic

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-10-30 : 12:22:18

Hi All.

I'm trying to build a variable up from 3 fields, a string, and two integers. However once I've converted the integers into strings, they seem to just repeat in the results but the string field does not.

Here's the code:

DECLARE @CityID as varchar
DECLARE @ID as varchar
SET @CityID = CONVERT(varchar, 20)
SET @ID = CONVERT(varchar, 20)
SELECT Name + ',' + @CityID + ',' + @ID AS full_areadetails FROM Customers ORDER BY Name ASC

Thanks for any help :-)

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-10-30 : 13:13:35

Try this:


SELECT 
[Name] + ', ' + cast(CityID as varchar(20)) + ', ' + cast(ID as varchar(20)) AS full_areadetails 
FROM Customers 
ORDER BY [Name] ASC

Best

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 13:19:36

in case any of fields are nullable make it like


SELECT 
COALESCE([Name] + ', ','') + COALESCE(cast(CityID as varchar(20)) + ', ','') + COALESCE(cast(ID as varchar(20)),'') AS full_areadetails 
FROM Customers 
ORDER BY [Name] ASC

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

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-31 : 03:36:48

SELECT Name + ',' + CONVERT(VARCHAR(20),CityID )+ ',' + CONVERT(VARCHAR(20),ID) AS full_areadetails FROM Customers ORDER BY Name ASC

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-10-31 : 10:04:56

Perfect, thanks to all. CAST did the trick, did'nt know that one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 11:18:05

quote:
Originally posted by PeteLeHoq

Perfect, thanks to all. CAST did the trick, did'nt know that one.

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources