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)
 Placing comma in a variable from different rows

Author  Topic 

umertahir
Posting Yak Master

154 Posts
Posted - 2009-03-09 : 11:30:59
Hi all,

I need a little help to put comma at the right place, depending if there are more than one rows returned from the query, on my piece of string from the following code:


DECLARE @currentColumnName VARCHAR(50)
SET @currentColumnName = ''

SELECT	@currentColumnName =  @currentColumnName + ', ' + tB.chrColumnName
FROM	dbo.tblLandcatchEntityEventType AS tA
			INNER JOIN dbo.tblLandcatchEntityEventTypeColumnNames AS tB
				ON tA.intEventTypeID = tB.intEventTypeID
			LEFT OUTER JOIN #tblTempTableWithColumnOrderAndName AS tSheet
				ON	tB.intColumnOrderNumber = tSheet.intColumnId AND
					tB.chrColumnName		= tSheet.chrColumnName
WHERE	tA.chrEventName = @tableName AND
		tSheet.intColumnId IS NULL
ORDER BY tB.intColumnOrderNumber


Result:
, Site, YearClass


I want the result to be like:
Expected Result:
Site, YearClass


and incase if there is only one row then
Expected Result for one row rproduced:
Site

Thanks
Umer

umertahir
Posting Yak Master

154 Posts
Posted - 2009-03-09 : 11:48:05
I have done it the following way which is working for me:


SELECT	@currentColumnName =	CASE
									WHEN
										@currentColumnName = ''
									THEN
										tB.chrColumnName

									WHEN 
										@currentColumnName <> '' 
									THEN  
										@currentColumnName + ', ' + tB.chrColumnName
								END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-03-10 : 03:29:41
quote:
Originally posted by umertahir

Hi all,

I need a little help to put comma at the right place, depending if there are more than one rows returned from the query, on my piece of string from the following code:


DECLARE @currentColumnName VARCHAR(50)
SET @currentColumnName = ''

SELECT	@currentColumnName =  COALESECE(@currentColumnName + ', ','') + tB.chrColumnName
FROM	dbo.tblLandcatchEntityEventType AS tA
			INNER JOIN dbo.tblLandcatchEntityEventTypeColumnNames AS tB
				ON tA.intEventTypeID = tB.intEventTypeID
			LEFT OUTER JOIN #tblTempTableWithColumnOrderAndName AS tSheet
				ON	tB.intColumnOrderNumber = tSheet.intColumnId AND
					tB.chrColumnName		= tSheet.chrColumnName
WHERE	tA.chrEventName = @tableName AND
		tSheet.intColumnId IS NULL
ORDER BY tB.intColumnOrderNumber


Result:
, Site, YearClass


I want the result to be like:
Expected Result:
Site, YearClass


and incase if there is only one row then
Expected Result for one row rproduced:
Site

Thanks
Umer

do like above
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-03-10 : 04:35:17
if ur using 2005 & above
try this too
SELECT stuff(@currentColumnName,1,1,'')
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-03-10 : 04:45:04
quote:
Originally posted by bklr

if ur using 2005 & above
try this too
SELECT stuff(@currentColumnName,1,1,'')

stuff works even in sql 2000
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-03-10 : 04:58:12
quote:
Originally posted by visakh16

quote:
Originally posted by bklr

if ur using 2005 & above
try this too
SELECT stuff(@currentColumnName,1,1,'')

stuff works even in sql 2000

i haven't tried in sql 2000 so that i mentioned 2005 onwards
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-03-10 : 05:03:44
quote:
Originally posted by bklr

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

if ur using 2005 & above
try this too
SELECT stuff(@currentColumnName,1,1,'')

stuff works even in sql 2000

i haven't tried in sql 2000 so that i mentioned 2005 onwards

you need to make sure you check something before suggesting in future
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-03-10 : 05:07:18
quote:
Originally posted by visakh16

quote:
Originally posted by bklr

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

if ur using 2005 & above
try this too
SELECT stuff(@currentColumnName,1,1,'')

stuff works even in sql 2000

i haven't tried in sql 2000 so that i mentioned 2005 onwards

you need to make sure you check something before suggesting in future

so that only i have mentioned 2005 and above it will works
Go to Top of Page

umertahir
Posting Yak Master

154 Posts
Posted - 2009-03-10 : 05:25:40
Msg 195, Level 15, State 10, Line 93
'COALESECE' is not a recognized built-in function name.

quote:
Originally posted by visakh16

quote:
Originally posted by umertahir

Hi all,

I need a little help to put comma at the right place, depending if there are more than one rows returned from the query, on my piece of string from the following code:


DECLARE @currentColumnName VARCHAR(50)
SET @currentColumnName = ''

SELECT	@currentColumnName =  COALESECE(@currentColumnName + ', ','') + tB.chrColumnName
FROM	dbo.tblLandcatchEntityEventType AS tA
			INNER JOIN dbo.tblLandcatchEntityEventTypeColumnNames AS tB
				ON tA.intEventTypeID = tB.intEventTypeID
			LEFT OUTER JOIN #tblTempTableWithColumnOrderAndName AS tSheet
				ON	tB.intColumnOrderNumber = tSheet.intColumnId AND
					tB.chrColumnName		= tSheet.chrColumnName
WHERE	tA.chrEventName = @tableName AND
		tSheet.intColumnId IS NULL
ORDER BY tB.intColumnOrderNumber


Result:
, Site, YearClass


I want the result to be like:
Expected Result:
Site, YearClass


and incase if there is only one row then
Expected Result for one row rproduced:
Site

Thanks
Umer

do like above
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-03-10 : 05:26:59
quote:
Originally posted by umertahir

Msg 195, Level 15, State 10, Line 93
'COALESECE' is not a recognized built-in function name.

quote:
Originally posted by visakh16

quote:
Originally posted by umertahir

Hi all,

I need a little help to put comma at the right place, depending if there are more than one rows returned from the query, on my piece of string from the following code:


DECLARE @currentColumnName VARCHAR(50)
SET @currentColumnName = ''

SELECT	@currentColumnName =  COALESCE(@currentColumnName + ', ','') + tB.chrColumnName
FROM	dbo.tblLandcatchEntityEventType AS tA
			INNER JOIN dbo.tblLandcatchEntityEventTypeColumnNames AS tB
				ON tA.intEventTypeID = tB.intEventTypeID
			LEFT OUTER JOIN #tblTempTableWithColumnOrderAndName AS tSheet
				ON	tB.intColumnOrderNumber = tSheet.intColumnId AND
					tB.chrColumnName		= tSheet.chrColumnName
WHERE	tA.chrEventName = @tableName AND
		tSheet.intColumnId IS NULL
ORDER BY tB.intColumnOrderNumber


Result:
, Site, YearClass


I want the result to be like:
Expected Result:
Site, YearClass


and incase if there is only one row then
Expected Result for one row rproduced:
Site

Thanks
Umer

do like above


thats a typo it should be COALESCE
Go to Top of Page

umertahir
Posting Yak Master

154 Posts
Posted - 2009-03-10 : 06:14:19
Result:
, Site, YearClass


quote:
Originally posted by visakh16

quote:
Originally posted by umertahir

Msg 195, Level 15, State 10, Line 93
'COALESECE' is not a recognized built-in function name.

quote:
Originally posted by visakh16

quote:
Originally posted by umertahir

Hi all,

I need a little help to put comma at the right place, depending if there are more than one rows returned from the query, on my piece of string from the following code:


DECLARE @currentColumnName VARCHAR(50)
SET @currentColumnName = ''

SELECT	@currentColumnName =  COALESCE(@currentColumnName + ', ','') + tB.chrColumnName
FROM	dbo.tblLandcatchEntityEventType AS tA
			INNER JOIN dbo.tblLandcatchEntityEventTypeColumnNames AS tB
				ON tA.intEventTypeID = tB.intEventTypeID
			LEFT OUTER JOIN #tblTempTableWithColumnOrderAndName AS tSheet
				ON	tB.intColumnOrderNumber = tSheet.intColumnId AND
					tB.chrColumnName		= tSheet.chrColumnName
WHERE	tA.chrEventName = @tableName AND
		tSheet.intColumnId IS NULL
ORDER BY tB.intColumnOrderNumber


Result:
, Site, YearClass


I want the result to be like:
Expected Result:
Site, YearClass


and incase if there is only one row then
Expected Result for one row rproduced:
Site

Thanks
Umer

do like above


thats a typo it should be COALESCE
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-03-10 : 06:21:40
show your code. i dont think posted code will give you leading ,
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-03-10 : 06:22:20
did u tried by using stuff
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-03-10 : 06:30:53
also you should have concat null yields null property set to true for above to work. what does this return?

EXEC sp_dboption 'your db name here','concat null yields null'
Go to Top of Page

santosh2307
Starting Member

7 Posts
Posted - 2009-03-10 : 08:54:02
Check patindex

declare @var varchar(40)

set @var=',Site, YearClass'
select case when patindex(',',@var)=0 then substring(@var,(patindex(',',@var))+ 2,len(@var)) else @var end

Hope this helps u.





quote:
Originally posted by umertahir

Hi all,

I need a little help to put comma at the right place, depending if there are more than one rows returned from the query, on my piece of string from the following code:


DECLARE @currentColumnName VARCHAR(50)
SET @currentColumnName = ''

SELECT	@currentColumnName =  @currentColumnName + ', ' + tB.chrColumnName
FROM	dbo.tblLandcatchEntityEventType AS tA
			INNER JOIN dbo.tblLandcatchEntityEventTypeColumnNames AS tB
				ON tA.intEventTypeID = tB.intEventTypeID
			LEFT OUTER JOIN #tblTempTableWithColumnOrderAndName AS tSheet
				ON	tB.intColumnOrderNumber = tSheet.intColumnId AND
					tB.chrColumnName		= tSheet.chrColumnName
WHERE	tA.chrEventName = @tableName AND
		tSheet.intColumnId IS NULL
ORDER BY tB.intColumnOrderNumber


Result:
, Site, YearClass


I want the result to be like:
Expected Result:
Site, YearClass


and incase if there is only one row then
Expected Result for one row rproduced:
Site

Thanks
Umer
Go to Top of Page

umertahir
Posting Yak Master

154 Posts
Posted - 2009-03-10 : 09:18:58
Guys my CASE statement works fine, please suggest if there is something wrong or some scenario which I have not covered in it.
Rest of the functions suggested by all you gurus sounds good but kind of complicated to me.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site