Converting multiple rows as CSV string

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

Transact-SQL (2000)

Converting multiple rows as CSV string

Author

Topic

Kaleem021
Starting Member

26 Posts

Posted - 2005-09-07 : 01:54:53

Dear All,
I am trying to select multiple values as a CSV String. I have gone through [url]http://sqlteam.com/forums/topic.asp?TOPIC_ID=50011[/url] topic and implement UDF but it return all values including dupicates whereas my required CSV string should not contain any duplicates.
At present, I have done this using cursor. Please let me know if this can be done wihtout cursors.

Regards,

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-07 : 10:54:42

It is difficult to help you without posting some of your code. Give us the table scripts, some sample data, and your desired resultset.

Thanks!

Nathan Skerl

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 01:58:42

You should use something like this

declare @Sql varchar(8000)
Select @sql=isnull(@sql+',','')+col from (select distinct col from yourTable) t
Select @sql

Madhivanan

Failing to plan is Planning to fail

Kaleem021
Starting Member

26 Posts

Posted - 2005-09-08 : 03:40:43

Thanks Madhivanan

Initially I did it using Cursors.

USE PUBS
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FnGetCity]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FnGetCity]
GO

CREATE FUNCTION FnGetCity ()  
RETURNS varchar(800) AS  
BEGIN 
Declare @Ret varchar(300),@City varchar(25)
Set @Ret=''
Declare CrsrCity Cursor for
		Select Distinct City From Authors
Open CrsrCity
Fetch Next From CrsrCity into @City
While @@Fetch_Status=0
Begin
	Set @Ret = @Ret +  Case When @Ret='' Then '' Else ', ' End + @City
	Fetch Next From CrsrCity Into @City
End
Close CrsrCity
Deallocate CrsrCity
Return (@Ret)
END

Then I switch to temporary table and replace cursor part with

CREATE TABLE #T(City varchar(25))
Insert Into #T Select Distinct City From Authors
Select @City = @City + ', ' + City from #T
Select Substring(@City,3,800)
Drop Table #T

But I think your solution is more efficient.

Thanks again.

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 04:02:28

Dont use temporary table
Use the query something similar to the one I suggested

For multiple ids, make use of Functions and refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

Kaleem021
Starting Member

26 Posts

Posted - 2005-09-13 : 07:35:07

I dont know why but Its not working with Order by Clause. It runs as expected if I remove Cast function.

Use Pubs
declare @Sql varchar(8000)
Set @Sql = ''
Select @sql=@sql+' '+ city from authors Order By cast(zip as bigint)
Select @sql

Any suggestion would be a great helpl

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 07:40:30

What is the datatype of zip and why do you want it to cast to bigint?

Madhivanan

Failing to plan is Planning to fail

Kaleem021
Starting Member

26 Posts

Posted - 2005-09-14 : 02:07:16

Its datatype is char(5) and you can run this query against PUBS database. I need CSV String to be sorted on zip code and cities having low numeric value of zip code should come first.

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-14 : 02:23:22

Wont this work?

declare @Sql varchar(8000)
Set @Sql = ''
Select @sql=@sql+' '+ city from Pubs..authors Order By zip
Select @sql

Madhivanan

Failing to plan is Planning to fail

Kaleem021
Starting Member

26 Posts

Posted - 2005-09-14 : 07:32:24

Yes it works because zip code length is same for all entries.
I think I should explain the actual problem.
Here is the table, data and query.

CREATE TABLE SeaLiner_Marks_Desc (
[BLNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Cargo_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Desc_Or_Mark] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Seq_No] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','1','SHIPPER''S LOAD COUNT AND SEALED')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','2','1 X 20'' CONTAINER STC:-')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','3','1 UNIT CAR')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','4','CHASE NO. JTERB71J500025778')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','5','FREIGHT PREPAID.')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','6','"CARGO IN TRANSIT TO KABUL BY TRANSLOG O')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','7','CONSIGNEE''S RISK & EXPENSE. CARRIER''S L')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','8','CEASES AT KARACHI AS PORT OF DISCHARGE."')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','9','SHIPPED ON BOARD DATE: 10/09/2005')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','10','ED NO. 13-1-5-00290235')
GO
declare @Sql varchar(8000)
Set @Sql = ''
Select @sql=@sql+' '+ IsNull([Description],'') from SeaLiner_Marks_Desc Order By Cast(Seq_No as int)
Select @sql
--Returns only last row's data though it should concatenate all rows.
GO
Drop Table SeaLiner_Marks_Desc
Go

I think Cargo_Id and Seq_No datatype should be int but I can't change them.

Thanks for your cooperation.

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

Kaleem021
Starting Member

26 Posts

Posted - 2005-09-14 : 07:32:56

Yes it works because zip code length is same for all entries.
I think I should explain the actual problem.
Here is the table, data and query.

CREATE TABLE SeaLiner_Marks_Desc (
[BLNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Cargo_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Desc_Or_Mark] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Seq_No] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','1','SHIPPER''S LOAD COUNT AND SEALED')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','2','1 X 20'' CONTAINER STC:-')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','3','1 UNIT CAR')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','4','CHASE NO. JTERB71J500025778')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','5','FREIGHT PREPAID.')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','6','"CARGO IN TRANSIT TO KABUL BY TRANSLOG O')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','7','CONSIGNEE''S RISK & EXPENSE. CARRIER''S L')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','8','CEASES AT KARACHI AS PORT OF DISCHARGE."')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','9','SHIPPED ON BOARD DATE: 10/09/2005')
INSERT INTO [SeaLiner_Marks_Desc] VALUES ('DXB0006869','2','D','10','ED NO. 13-1-5-00290235')
GO
declare @Sql varchar(8000)
Set @Sql = ''
Select @sql=@sql+' '+ IsNull([Description],'') from SeaLiner_Marks_Desc Order By Cast(Seq_No as int)
Select @sql
--Returns only last row's data though it should concatenate all rows.
GO
Drop Table SeaLiner_Marks_Desc
Go

I think Cargo_Id and Seq_No datatype should be int but I can't change them.

Thanks for your coopration.

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-14 : 07:45:05

Why did you use varchar datatype to store numbers?
Use integer datatype

Madhivanan

Failing to plan is Planning to fail

Kaleem021
Starting Member

26 Posts

Posted - 2005-09-14 : 08:09:44

Its a third party table. We can only extract data from there. Any suggestion without modifying table structure?

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-14 : 08:44:06

I think, if possible create a dummy table same as that of original table with having int datatype for seq_no and insert records to it and do query based on that table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources