Solved

Conver table column to CSV

Posted on 2010-11-17
12
480 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

749 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