how to display colums separated by comma without using cursor?
-
Hi all,
I'm a beginner in sqlserver2000.
I have a small question.
I have a small table as follows:
company products
------- --------
123 pencil
123 ink
123 fillers
123 cartridge
124 apple
124 orange
The objective is to display company and its products separated by commas.
123 pencil,ink,fillers,cartridge
124 apple,orange
I did get the job done by creating a cursor by storing those two values in two temp variables and going row by row appending the characters. Since cursors are said to be slow, can this be done by select statements?
thank you for any ideas.
su
-
Well the first answer is do it client side. But we always follow with this :
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROMdbo.SysColumns C
WHEREC.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BYC.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
-
A cleaner solution and an introduction to a very useful function if you've never used it.
CREATE FUNCTION dbo.LIST_OF_VALUES (@ColumnId as int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @Items as varchar(8000)
SELECT
@Items = COALESCE(@Items + ',' + table_name.column_name, table_name.column_name)
FROM
table_name
WHERE
primary_key_field = @ColumnId
END
The COALESCE takes the first non-null value in a comma-seperated list. So in this case, since @Items was not instantiated (<null>
, when it is added to anything it results in a null. Therefore, @items becomes the column value by itself. When it gets to the second record, @Items is no longer null and can be appended to by the comma and the new row column value. -
;)DECLARE @Items as varchar(8000)
SELECT
@Items = COALESCE(@Items + ',' + colsName , +ColsName)
FROM TableName
select @Items:P
-
;)DECLARE @Items as varchar(8000)
SELECT
@Items = COALESCE(@Items + ',' + colsName , +ColsName)
FROM TableName
select @Items:P
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply