?
Solved

Conver table column to CSV

Posted on 2010-11-17
12
Medium Priority
?
484 Views
Last Modified: 2012-05-10
I need to convert a table column into csv.

Example.

Table A
->a_brandID

Table B
->b_brandID
->seriesID

to get
[a_id],[seriesID,seriesID,seriesID,seriesID]

I came across this User Defined Aggregate on sqlauthority http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/, but have no idea how to implement it.

[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class toCSV : IBinarySerialize
{
///
/// The variable that holds the intermediate result of the concatenation
///
private StringBuilder intermediateResult;

///
/// Initialize the internal data structures
///
public void Init()
{
this.intermediateResult = new StringBuilder();
}

///
/// Accumulate the next value, not if the value is null
///
///
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}

this.intermediateResult.Append(value.Value).Append(‘,’);
}

///
/// Merge the partially computed aggregate with this aggregate.
///
///
public void Merge(toCSV other)
{
this.intermediateResult.Append(other.intermediateResult);
}

///
/// Called at the end of aggregation, to return the results of the aggregation.
///
///
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length – 1);
}

return new SqlString(output);
}

public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}

public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}



I have gone through the msdn files and can't seem to get it working

http://msdn.microsoft.com/en-us/library/w2kae45k%28VS.80%29.aspx

The steps in the article seems to be leading me in circles.

I do not have the option to

-add new item---user defined function

the only option available in visual studio is sql server integration services.

Any instructions on how to get this up and running will be greatly appreciated.
0
Comment
Question by:av8or16
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34162319
This will convert a single column.

DECLARE @STRING
SET @STRING=''
SELECT @STRING=@STRING+[COLUMN]+',' FROM TABLE
SELECT LEFT(@STRING,LEN(@STRING)-1)

Open in new window


But to do what you want by also having a seperate column at the begining you may have to put it in a loop.

Will the be multiple rows at the end?
0
 

Author Comment

by:av8or16
ID: 34164127
Yes.  I will need multiple columns at the beginning and there will be multiple rows of returned data.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34164303
Here is an example against the sys.databases table using snapshot_isolation_desc as the grouped column.

declare @table table (col varchar(max))
declare @var2 varchar(max)
declare @var varchar(255)

declare loop_cursor cursor for
select distinct snapshot_isolation_state_desc from sys.databases

open loop_cursor
fetch next from loop_cursor
INTO @var

while @@FETCH_STATUS=0
begin
set @var2=''
	select @var2=@var2+name+',' from sys.databases where snapshot_isolation_state_desc=@var
	select @var2=@var+','+@var2
	insert into @table values (LEFT(@var2,len(@var2)-1))
	fetch next from loop_cursor into @var
end

select * from @table

close loop_cursor
deallocate loop_cursor

Open in new window


Does this help?
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:av8or16
ID: 34164414
No because it is only returning one column.  I need to use this to group my PK by its attributes ie,

id       attributes
==     ========
1        2,6,9,10,26
2        3,7,1,19,20
etc.

I want to turn this query into a view for further use.

The User Defined Aggregate I specified in the questing does exactly what I need.  I just need help getting it "installed" into sql server.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34164585
Would you be happy with creating a view and a function?
0
 

Author Comment

by:av8or16
ID: 34164701
I will be happy with anything that gets the job done!
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34164801
Ok hows this.

create function fn_GroupData (@brand_id int)
returns VARCHAR(MAX)
AS
BEGIN
	DECLARE @brands VARCHAR(MAX)
	SET @brands=''
	SELECT @brands=@brands+brand+',' from tableb WHERE brand_id=@brand_id
	SELECT @brands=LEFT(@brands,(LEN(@brands)-1))
	RETURN(@brands)
END

Open in new window


CREATE VIEW vw_GroupedBrands
AS
SELECT DISTINCT brand_id,dbo.fn_GroupData(brand_id) FROM TABLEB

Open in new window

0
 

Author Comment

by:av8or16
ID: 34165138
I know next to nothing about implementing functions...

If you can bear with my lack of knowledge and show how I would plug in the following...

tableName -- series

seriesID        regionid
==                ======
1                   1
1                   2
1                   5
2                   2
2                   3
etc.

I appreciate the help.  Also, this will need to be done on a lot of tables.  Will I need to create a function for each one?

0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 2000 total points
ID: 34165244
You dont make it easy do ya ;)

Unless you want a stored procedure instead of a view you will have to create a function & view per set of tables.

also it looks like you actually want to use a different data type than you originally specified in the question. This will involve re-writting the function.

create function fn_GroupData (@series_id int)
returns VARCHAR(MAX)
AS
BEGIN
	DECLARE @regions VARCHAR(MAX)
	SET @regions=''
	SELECT @regions=@regions+CONVERT(VARCHAR(15),region_id)+',' from series 
                WHERE brand_id=@series_id	
                SELECT @regions=LEFT(@regions,(LEN(@regions)-1))
	RETURN(@regions)
END

Open in new window

CREATE VIEW vw_GroupedBrands
AS
SELECT DISTINCT series_id,dbo.fn_GroupData(series_id) FROM series 

Open in new window

0
 

Author Comment

by:av8or16
ID: 34165448
I recive this error when I try to create the view.  Here is what I plugged in..


Msg 4511, Level 16, State 1, Procedure vw_GroupData, Line 3
Create View or Function failed because no column name was specified for column 2.

create function fn_GroupData (@seriesID int)
returns VARCHAR(MAX)
AS

BEGIN
      DECLARE @RegionID VARCHAR(MAX)
      SET @RegionID=''
      SELECT @RegionID=@RegionID+CONVERT(varchar(500),COALESCE(RegionID,0))+',' from seriesDetail WHERE SeriesID = @SeriesID
      SELECT @RegionID=LEFT(@RegionID,(LEN(@RegionID)-1))
      RETURN(@RegionID)
END

GO



CREATE VIEW vw_GroupData
AS
SELECT DISTINCT SeriesID,dbo.fn_GroupData(SeriesID) FROM seriesDetail


0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34165488
Sorry forgot about that.

CREATE VIEW vw_GroupData
AS
SELECT DISTINCT SeriesID,dbo.fn_GroupData(SeriesID) AS 'Attributes' FROM seriesDetail
0
 

Author Closing Comment

by:av8or16
ID: 34165550
Fantastic.  Thanks for all your help.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question