Table to CSV? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Table to CSV?

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-11-14 : 08:36:27

I have the CSV to table thing down pat, but I frequently find myself needing to take the results of query, and assemble them into a delimited list. Right now I do this by creating a new function each time I want to do this somewhere, and the function contains code like this:


Create Function dbo.trn_ListDatabaseTranslationDefinitionFields
	(
	@DatabaseTranslationID int
	)
	Returns varchar(8000)
As
Begin

Declare @fields varchar(8000)

	Select @fields = Coalesce(@fields + ', ', '') + FieldName
	From dbo.trn_tblDatabaseTranslationDefinitionFields
	Where DatabaseTranslationID = @DatabaseTranslationID
	Order By FieldName

return IsNull(@fields, '')

End
GO
SELECT
	SomeID,
	SomeVal,
	ThatList = dbo.trn_trn_ListDatabaseTranslationDefinitionFields(SomeID)
FROM
	SomeTable

...and I do this *every* freaking time I need some form a delimited list. I was hoping for some way in which I could just have one function to do it, but short of dynamic SQL I don't think it's possible, am I correct?

My reasoning is that UDFs, and SPs cannot take in a table as a parameter. Since this needs to be called while in a SELECT query I can't create a temp table for the UDF to look for.

Anyone know if I'm missing something? Is there no way to do this other than rewrite the query in each place it's needed, or go with dynamic SQL?

Thanks,
Steve

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-14 : 14:18:26

I'm interested too. If I have a one-time CSV need, I run it through Query Analyzer.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-14 : 14:36:02

Well what are you doing with the data...I'm assuming it's going to a flat file, right?

Why not use bcp with a format card?

Brett

8-)

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-11-14 : 16:25:01

In my case the table to CSV is for display on a web page. Rather than bring back two sets of data, and assemble it during display(inside of an ASP.Net Repeater control), I have always found it more convenient to build the list in SQL Server, so that I can just bring back this one string of data for easy display anywhere.

Steve

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-14 : 16:30:02

Yeah, we do the same thing..but it's from scratch every time...

What do you do about a header...

I add it...and if the query get's hairy, I format the results and select it from a derived table

I have to look into that function you posted..



	Select 	  
		   '"Proof_Type"'
		+ ',"Work_Group"'
		+ ',"Status"'
		+ ',"Period Type"'
		+ ',"Proof Year"'
		+ ',"Proof Period"'
		+ ',"Count"'
     Union All



	Select  
		   '"'+RTrim(IsNull(Proof_Type,''))+'"'
		+ ',"'+RTrim(IsNull(Work_Group_Desc,''))+'"'
		+ ',"'+RTrim(IsNull(Status_Desc,''))+'"'
		+ ',"'+RTrim(IsNull(Period_Type,''))+'"'
		+ ',"'+RTrim(IsNull(Proof_Year,''))+'"'
		+ ',"'+RTrim(IsNull(Proof_Period,''))+'"'
		+ ',"'+RTrim(Convert(Char(15),IsNull(Number_Of_Proofs,0)))+'"'
	  From (

	Select	  'WH_Proof' As Proof_Type
		, w.Work_Group_Desc
		, s.Status_Desc
		, p.Period_Type
		, p.Proof_Year
		, p.Proof_Period
		, Count(*) As Number_Of_Proofs
	  From (((WH_Proof p (NoLock)
     Left Join    WH_Proof_List l (NoLock)
	    On	  p.Company_Name = l.Company_Name
	   And    p.Tax_Agency_Id = l.Tax_Agency_Id
	   And    p.Region = l.Region
	   And    p.Tax_Class = l.Tax_Class
	   And    p.Sub_Region = l.Sub_Region) 
     Left Join	  Work_Group w
	    On	  l.Work_Group = w.Work_Group) 
     Left Join	  Proof_Status s
	    On	  p.Status = s.Status)
      Group By    w.Work_Group_Desc
		, s.Status_Desc
		, p.Period_Type
		, p.Proof_Year
		, p.Proof_Period

     Union All

	
	Select	  'Disb_Proof'
		, w.Work_Group_Desc
		, s.Status_Desc
		, p.Period_Type
		, p.Proof_Year
		, p.Proof_Period
		, Count(*) As Number_Of_Proofs
	  From (((Disb_Proof p (NoLock)
     Left Join    Disb_Proof_List l (NoLock)
	    On	  p.Company_Name = l.Company_Name
	   And    p.Tax_Agency_Id = l.Tax_Agency_Id
	   And    p.Region = l.Region
	   And    p.Tax_Class = l.Tax_Class) 
     Left Join	  Work_Group w
	    On	  l.Work_Group = w.Work_Group) 
     Left Join	  Proof_Status s
	    On	  p.Status = s.Status)
      Group By    w.Work_Group_Desc
		, s.Status_Desc
		, p.Period_Type
		, p.Proof_Year
		, p.Proof_Period

     Union All

	Select	  'Company_Return'
		, w.Work_Group_Desc
		, s.Status_Desc
		, 'Q' As Period_Type
		, p.Proof_Year
		, p.Proof_Period
		, Count(*) As Number_Of_Proofs
	  From (((Company_Return p (NoLock)
     Left Join    Company_Return_List l (NoLock)
	    On	  p.Company_Name = l.Company_Name
	   And    p.Tax_Agency_Id = l.Tax_Agency_Id
	   And    p.Return_Type = l.Return_Type) 
     Left Join	  Work_Group w
	    On	  l.Work_Group = w.Work_Group) 
     Left Join	  Proof_Status s
	    On	  p.Status = s.Status)
      Group By    w.Work_Group_Desc

		, s.Status_Desc
--		, p.Period_Type
		, p.Proof_Year
		, p.Proof_Period

) As A

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources