[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

Conver table column to CSV

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
av8or16
Asked:
av8or16
  • 6
  • 6
1 Solution
 
EvilPostItCommented:
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
 
av8or16Author Commented:
Yes.  I will need multiple columns at the beginning and there will be multiple rows of returned data.
0
 
EvilPostItCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
av8or16Author Commented:
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
 
EvilPostItCommented:
Would you be happy with creating a view and a function?
0
 
av8or16Author Commented:
I will be happy with anything that gets the job done!
0
 
EvilPostItCommented:
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
 
av8or16Author Commented:
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
 
EvilPostItCommented:
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
 
av8or16Author Commented:
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
 
EvilPostItCommented:
Sorry forgot about that.

CREATE VIEW vw_GroupData
AS
SELECT DISTINCT SeriesID,dbo.fn_GroupData(SeriesID) AS 'Attributes' FROM seriesDetail
0
 
av8or16Author Commented:
Fantastic.  Thanks for all your help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now