Aggregate/Concatenate Strings in a Script Component?

Hi--

I am uncertain how to do this. I am thinking it could be done in a script component, but after a day of experimentation, I'm not getting any closer. I'd also like to know if there is another component I may be able to use for this.

I have data coming from an Excel Spreadsheet that looks like this:

CustNumb Invoice

1 a

1 b

2 c

3 d

3 e

3 f

I would like an output that looks like this:

CustNumb Invoice

1 a, b

2 c

3 d, e, f

I am not even sure if I should be trying to do this in the subroutine that looks at each row or the one that looks at the entire buffer.

Thanks for any help or ideas...

Hilary

If you know how the maximum number of values you might have, you might be able to do this in a pivot transform. However, I would probably do it in a script. You'd need to set up the output as asynchronous (make the SynchronousOutputID = None on the output). In the ProcessInputRow method, you'll need to out a row on the async output each time the CustNumb changes from the previous value using the Output0Buffer.AddRow method.

|||

Thank you for your reply. When I was trying to do this in script earlier, that was one of the things I had trouble understanding--how to find out when the CustNumb changed. I thought that ProcessInputRow was looking at one row at a time, so I tried grabbing the customer number and then checking the next row to see if it matched... something like:

Code Snippet

Dim intCustNumber as Integer

intCustNumber = Row.CustNumber

With Row.NextRow

Dim intCustNumber1 as Integer

intCustNumber1 = Row.CustNumber

If intCustNumber = intCustNumber1 then

Etc.... not surprisingly, I lost all the rows that didn't have a repetition of the customer number. How do I look at each row and know when the number changes?

Thank you,

Hilary

|||

ProcessInputRow does look at a row each time. You need to declare a variable at the instance level (or a static variable, but I prefer instance) to hold the customer number. I freehanded the code below, so it may not compile, but hopefully it gives you an idea. You'll need to make sure it handles the first row and last row properly - I haven't tested it.

Code Snippet

Public Class ScriptMain

Inherits UserComponent

Private intCustomer As Integer

Private strString As String

Private blnFirstRow As Boolean = True

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Row.CustNumber = intCustomer Then

'Same customer

strString = strString + Row.ColumnVal

Else

If blnFirstRow Then

blnFirstRow = False

Else

'new customer - output the current values

Output0Buffer.AddRow()

Output0Buffer.CustNumber = intCustomer

Output0Buffer.NewCol = strString

End If

'now reset the variables

intCustomer = Row.CustNumber

strString = Row.ColumnVal

End If

End Sub

Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer)

MyBase.Input0_ProcessInput(Buffer)

If Row.EndOfRowset Then

Output0Buffer.AddRow()

Output0Buffer.CustNumber = intCustomer

Output0Buffer.NewCol = strString

Output0Buffer.SetEndOfRowSet()

End If

End Sub

|||

Thank you, that is just what I needed-- a bit of a map for how to approach it.

Hilary

|||

There was a bug in the script above, where the last row could be skipped. I found it when when I was writing this problem up to post on my blog. Here's the full sample:

http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx

I've also edited the script above to correct the issue.

|||

Thank you so very much. That is just above and beyond, you know? I really appreciate the help.

Hilary

Aggregate/Concatenate Strings in a Script Component?

Hi--

I am uncertain how to do this. I am thinking it could be done in a script component, but after a day of experimentation, I'm not getting any closer. I'd also like to know if there is another component I may be able to use for this.

I have data coming from an Excel Spreadsheet that looks like this:

CustNumb Invoice

1 a

1 b

2 c

3 d

3 e

3 f

I would like an output that looks like this:

CustNumb Invoice

1 a, b

2 c

3 d, e, f

I am not even sure if I should be trying to do this in the subroutine that looks at each row or the one that looks at the entire buffer.

Thanks for any help or ideas...

Hilary

If you know how the maximum number of values you might have, you might be able to do this in a pivot transform. However, I would probably do it in a script. You'd need to set up the output as asynchronous (make the SynchronousOutputID = None on the output). In the ProcessInputRow method, you'll need to out a row on the async output each time the CustNumb changes from the previous value using the Output0Buffer.AddRow method.

|||

Thank you for your reply. When I was trying to do this in script earlier, that was one of the things I had trouble understanding--how to find out when the CustNumb changed. I thought that ProcessInputRow was looking at one row at a time, so I tried grabbing the customer number and then checking the next row to see if it matched... something like:

Code Snippet

Dim intCustNumber as Integer

intCustNumber = Row.CustNumber

With Row.NextRow

Dim intCustNumber1 as Integer

intCustNumber1 = Row.CustNumber

If intCustNumber = intCustNumber1 then

Etc.... not surprisingly, I lost all the rows that didn't have a repetition of the customer number. How do I look at each row and know when the number changes?

Thank you,

Hilary

|||

ProcessInputRow does look at a row each time. You need to declare a variable at the instance level (or a static variable, but I prefer instance) to hold the customer number. I freehanded the code below, so it may not compile, but hopefully it gives you an idea. You'll need to make sure it handles the first row and last row properly - I haven't tested it.

Code Snippet

Public Class ScriptMain

Inherits UserComponent

Private intCustomer As Integer

Private strString As String

Private blnFirstRow As Boolean = True

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Row.CustNumber = intCustomer Then

'Same customer

strString = strString + Row.ColumnVal

Else

If blnFirstRow Then

blnFirstRow = False

Else

'new customer - output the current values

Output0Buffer.AddRow()

Output0Buffer.CustNumber = intCustomer

Output0Buffer.NewCol = strString

End If

'now reset the variables

intCustomer = Row.CustNumber

strString = Row.ColumnVal

End If

End Sub

Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer)

MyBase.Input0_ProcessInput(Buffer)

If Row.EndOfRowset Then

Output0Buffer.AddRow()

Output0Buffer.CustNumber = intCustomer

Output0Buffer.NewCol = strString

Output0Buffer.SetEndOfRowSet()

End If

End Sub

|||

Thank you, that is just what I needed-- a bit of a map for how to approach it.

Hilary

|||

There was a bug in the script above, where the last row could be skipped. I found it when when I was writing this problem up to post on my blog. Here's the full sample:

http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx

I've also edited the script above to correct the issue.

|||

Thank you so very much. That is just above and beyond, you know? I really appreciate the help.

Hilary

Aggregate Strings While Aggregating Data

I'm using the following query to determine the TotTons produced daily. A
sample data set is included.
On days that two grades are produced, I'd like the Grade name to be a
combination of the two grade names separated by a \. For example the desire
d
Grade name for 08-01-05 would be H\V. At this point I'm not concerned about
the order in which the letters appear in the new string.
Is this possible?
Thanks in advance,
Raul
SELECT
MAX(Datestamp1) AS DateStamp,
SUM(Tons) AS TotTons,
MAX(Grade) AS Grade 'just for example
FROM
(
SELECT
DateStamp as Datestamp1,
NumBatches,
Grade,
Tons
FROM
DailyTonsByGrade
) inrqry
GROUP BY Datestamp1
ORDER BY DateStamp ASC;
CREATE TABLE DailyTonsByGrade (
DateStamp smalldatetime,
NumBatches int,
Grade Varchar(20),
Tons real)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('06-01-05', 48, 'V', 403.2)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('07-01-05', 62, 'V', 520.8)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('08-01-05', 12, 'H', 112.8)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('08-01-05', 31, 'V', 285.6)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('09-01-05', 44, 'H', 413.6)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('10-01-05', 60, 'H', 564.0)
Or
DateStamp NumBatches Grade Tons
06-01-05 48 V 403.2
07-01-05 62 V 520.8
08-01-05 12 H 112.8
08-01-05 34 V 285.6
09-01-05 44 H 413.6
10-01-05 60 H 564would you ever have more than 2 grades on the same day ?
Message posted via http://www.webservertalk.com|||
In order to do that you need code that processes the multiple records in one
day's group, and outputs a concatenation of the Grade NAmes...
It can be done, but it involves row-processing (using a cursor, or a temp
table or table Variable) probably in a Stored Proc, or User efined FUnction.
One solution(using Latter)
would be
Create Functiondbo.FuelGrades(@.Dt DateTime)
Returns VarChar(500)
As
Begin
Declare @.Out VarChar(500) Set @.Out = ''
Declare @.Grade VarChar(50) Set @.Grade = ''
While Exists
(Select * From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade)
Select @.Out = @.Out + Min(Grade) + '/',
@.Grade = Min(Grade)
From DailyTonsByGrade
Where DateStamp = @.Dt
-- --
If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
Return @.Out
End
Then, in your query, just refer to this UDF...
Select Max(Datestamp) DateStamp,
Sum(Tons) TotTons,
dbo.FuelGrades(Datestamp) Grade
From DailyTonsByGrade
Group By Datestamp
Order By Max(Datestamp);
This will work, but performance will be poor...
"Raul" wrote:

> I'm using the following query to determine the TotTons produced daily. A
> sample data set is included.
> On days that two grades are produced, I'd like the Grade name to be a
> combination of the two grade names separated by a \. For example the desi
red
> Grade name for 08-01-05 would be H\V. At this point I'm not concerned abo
ut
> the order in which the letters appear in the new string.
> Is this possible?
> Thanks in advance,
> Raul
> SELECT
> MAX(Datestamp1) AS DateStamp,
> SUM(Tons) AS TotTons,
> MAX(Grade) AS Grade 'just for example
> FROM
> (
> SELECT
> DateStamp as Datestamp1,
> NumBatches,
> Grade,
> Tons
> FROM
> DailyTonsByGrade
> ) inrqry
> GROUP BY Datestamp1
> ORDER BY DateStamp ASC;
> CREATE TABLE DailyTonsByGrade (
> DateStamp smalldatetime,
> NumBatches int,
> Grade Varchar(20),
> Tons real)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('06-01-05', 48, 'V', 403.2)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('07-01-05', 62, 'V', 520.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 12, 'H', 112.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 31, 'V', 285.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('09-01-05', 44, 'H', 413.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('10-01-05', 60, 'H', 564.0)
> Or
> DateStamp NumBatches Grade Tons
> 06-01-05 48 V 403.2
> 07-01-05 62 V 520.8
> 08-01-05 12 H 112.8
> 08-01-05 34 V 285.6
> 09-01-05 44 H 413.6
> 10-01-05 60 H 564
>|||opps, I left out something...
THe UDF should be
Create Functiondbo.FuelGrades(@.Dt DateTime)
Returns VarChar(500)
As
Begin
Declare @.Out VarChar(500) Set @.Out = ''
Declare @.Grade VarChar(50) Set @.Grade = ''
While Exists
(Select * From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade)
Select @.Out = @.Out + Min(Grade) + '/',
@.Grade = Min(Grade)
From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade -- This is line I left Out
-- --
If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
Return @.Out
End
"Raul" wrote:

> I'm using the following query to determine the TotTons produced daily. A
> sample data set is included.
> On days that two grades are produced, I'd like the Grade name to be a
> combination of the two grade names separated by a \. For example the desi
red
> Grade name for 08-01-05 would be H\V. At this point I'm not concerned abo
ut
> the order in which the letters appear in the new string.
> Is this possible?
> Thanks in advance,
> Raul
> SELECT
> MAX(Datestamp1) AS DateStamp,
> SUM(Tons) AS TotTons,
> MAX(Grade) AS Grade 'just for example
> FROM
> (
> SELECT
> DateStamp as Datestamp1,
> NumBatches,
> Grade,
> Tons
> FROM
> DailyTonsByGrade
> ) inrqry
> GROUP BY Datestamp1
> ORDER BY DateStamp ASC;
> CREATE TABLE DailyTonsByGrade (
> DateStamp smalldatetime,
> NumBatches int,
> Grade Varchar(20),
> Tons real)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('06-01-05', 48, 'V', 403.2)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('07-01-05', 62, 'V', 520.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 12, 'H', 112.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 31, 'V', 285.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('09-01-05', 44, 'H', 413.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('10-01-05', 60, 'H', 564.0)
> Or
> DateStamp NumBatches Grade Tons
> 06-01-05 48 V 403.2
> 07-01-05 62 V 520.8
> 08-01-05 12 H 112.8
> 08-01-05 34 V 285.6
> 09-01-05 44 H 413.6
> 10-01-05 60 H 564
>|||if you are NEVER going to exceed 2 grade on any one day then this should
work
SELECT
MAX(Datestamp) AS DateStamp,
SUM(Tons) AS TotTons,
case
when MAX(Grade) <> Min(grade) then MAX(Grade)+ ''+ Min(grade)
else min(grade)
end AS Grade --just for example
FROM
DailyTonsByGrade
GROUP BY Datestamp
ORDER BY DateStamp ASC
Message posted via http://www.webservertalk.com|||It is unlikely that we will produce more than two grades in one day.
This is a pretty clever solution.
Thanks a bunch,
Raul
"baie dronk via webservertalk.com" wrote:

> if you are NEVER going to exceed 2 grade on any one day then this should
> work
> SELECT
> MAX(Datestamp) AS DateStamp,
> SUM(Tons) AS TotTons,
> case
> when MAX(Grade) <> Min(grade) then MAX(Grade)+ ''+ Min(grade)
> else min(grade)
> end AS Grade --just for example
> FROM
> DailyTonsByGrade
> GROUP BY Datestamp
> ORDER BY DateStamp ASC
> --
> Message posted via http://www.webservertalk.com
>|||I'll try this solution also.
Thank you,
Raul
"CBretana" wrote:
> opps, I left out something...
> THe UDF should be
> Create Functiondbo.FuelGrades(@.Dt DateTime)
> Returns VarChar(500)
> As
> Begin
> Declare @.Out VarChar(500) Set @.Out = ''
> Declare @.Grade VarChar(50) Set @.Grade = ''
> While Exists
> (Select * From DailyTonsByGrade
> Where DateStamp = @.Dt
> And Grade > @.Grade)
> Select @.Out = @.Out + Min(Grade) + '/',
> @.Grade = Min(Grade)
> From DailyTonsByGrade
> Where DateStamp = @.Dt
> And Grade > @.Grade -- This is line I left Out
> -- --
> If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
> Return @.Out
> End
>
> "Raul" wrote:
>

aggregate for strings

Any chance of getting an aggregate to combine varchar's in 08?

No. This is not planned for SQL Server 2008. You need to write your own SQLCLR aggregate or use traditional SQL techniques or xml capabilities to do the aggregation/concatenation.

|||

What specific aggregates are you interested in? There are some enhancements to the CLR user-defined aggregates in 2008 that may help you out.

Cheers,

-Isaac

|||

Ideally Sum(stringcolumn,separator) so Sum(MyColumn,':')

On the CLR:

When 05 was first released we thought that the CLR integration would be one of the best features in the product. However, once we actually tried to use it we realized that on average it was doubling the development time over T-SQL procedures/functions. The problem is that building a function in T-SQL is really easy, just load up management studio, edit the template, save and test. On the other hand building a CLR function requires at least a couple more steps if the developer has VS Pro and a lot more if they have VS Standard. This add's cost and time. This was semi alleviated by Neil's project which improved the entire CLR development experience. What needs to happen is that management studio needs to support CLR function development, with a simple one click save/deploy feature, even if it lacks full VS code functions. Likewise at the very least VS should support CLR function development directly off of the server/database explorer.

Edit: At the very least if SQL doesn't support that aggregate then RS should have one like that. Also how close are we to seeing the next CTP drop? I installed the July one on VPC then deleted it and made a new VPC to test another project. Now, I kinda want to reinstall it but I'm hesitating to because I figure that we will see a new CTP drop any day.

|||

Thanks for the feedback on CLR development. The issue of ease of developmentparticularly for what should be simple bits of code like the one we're talking about hereis one that we would like to do something about.

I don't have the date for the next CTP handy, but it's not immanentI wouldn't expect it before the end of October.

Cheers,

-Isaac

|||

Thanks you have been very helpful. We ended up using a CLR aggregate to solve our string problem. Just out of curiosity will we be able to use linq in the 08 CLR?

|||? LINQ isn't on the "short list" of .NET libraries tested and approved for direct loading in SQLCLR, if that's what you are asking. Cheers, Bob Beauchemin SQLskills ""akula"@.discussions.microsoft.com" <"=?UTF-8?B?YWt1bGE=?="@.discussions.microsoft.com> wrote in message news:8bd1a423-9adb-4b0b-9c22-0416c14805b4@.discussions.microsoft.com... Thanks you have been very helpful. We ended up using a CLR aggregate to solve our string problem. Just out of curiosity will we be able to use linq in the 08 CLR?

aggregate for strings

Any chance of getting an aggregate to combine varchar's in 08?

No. This is not planned for SQL Server 2008. You need to write your own SQLCLR aggregate or use traditional SQL techniques or xml capabilities to do the aggregation/concatenation.

|||

What specific aggregates are you interested in? There are some enhancements to the CLR user-defined aggregates in 2008 that may help you out.

Cheers,

-Isaac

|||

Ideally Sum(stringcolumn,separator) so Sum(MyColumn,':')

On the CLR:

When 05 was first released we thought that the CLR integration would be one of the best features in the product. However, once we actually tried to use it we realized that on average it was doubling the development time over T-SQL procedures/functions. The problem is that building a function in T-SQL is really easy, just load up management studio, edit the template, save and test. On the other hand building a CLR function requires at least a couple more steps if the developer has VS Pro and a lot more if they have VS Standard. This add's cost and time. This was semi alleviated by Neil's project which improved the entire CLR development experience. What needs to happen is that management studio needs to support CLR function development, with a simple one click save/deploy feature, even if it lacks full VS code functions. Likewise at the very least VS should support CLR function development directly off of the server/database explorer.

Edit: At the very least if SQL doesn't support that aggregate then RS should have one like that. Also how close are we to seeing the next CTP drop? I installed the July one on VPC then deleted it and made a new VPC to test another project. Now, I kinda want to reinstall it but I'm hesitating to because I figure that we will see a new CTP drop any day.

|||

Thanks for the feedback on CLR development. The issue of ease of developmentparticularly for what should be simple bits of code like the one we're talking about hereis one that we would like to do something about.

I don't have the date for the next CTP handy, but it's not immanentI wouldn't expect it before the end of October.

Cheers,

-Isaac

|||

Thanks you have been very helpful. We ended up using a CLR aggregate to solve our string problem. Just out of curiosity will we be able to use linq in the 08 CLR?

|||? LINQ isn't on the "short list" of .NET libraries tested and approved for direct loading in SQLCLR, if that's what you are asking. Cheers, Bob Beauchemin SQLskills ""akula"@.discussions.microsoft.com" <"=?UTF-8?B?YWt1bGE=?="@.discussions.microsoft.com> wrote in message news:8bd1a423-9adb-4b0b-9c22-0416c14805b4@.discussions.microsoft.com... Thanks you have been very helpful. We ended up using a CLR aggregate to solve our string problem. Just out of curiosity will we be able to use linq in the 08 CLR?

aggregate for strings

Any chance of getting an aggregate to combine varchar's in 08?

No. This is not planned for SQL Server 2008. You need to write your own SQLCLR aggregate or use traditional SQL techniques or xml capabilities to do the aggregation/concatenation.

|||

What specific aggregates are you interested in? There are some enhancements to the CLR user-defined aggregates in 2008 that may help you out.

Cheers,

-Isaac

|||

Ideally Sum(stringcolumn,separator) so Sum(MyColumn,':')

On the CLR:

When 05 was first released we thought that the CLR integration would be one of the best features in the product. However, once we actually tried to use it we realized that on average it was doubling the development time over T-SQL procedures/functions. The problem is that building a function in T-SQL is really easy, just load up management studio, edit the template, save and test. On the other hand building a CLR function requires at least a couple more steps if the developer has VS Pro and a lot more if they have VS Standard. This add's cost and time. This was semi alleviated by Neil's project which improved the entire CLR development experience. What needs to happen is that management studio needs to support CLR function development, with a simple one click save/deploy feature, even if it lacks full VS code functions. Likewise at the very least VS should support CLR function development directly off of the server/database explorer.

Edit: At the very least if SQL doesn't support that aggregate then RS should have one like that. Also how close are we to seeing the next CTP drop? I installed the July one on VPC then deleted it and made a new VPC to test another project. Now, I kinda want to reinstall it but I'm hesitating to because I figure that we will see a new CTP drop any day.

|||

Thanks for the feedback on CLR development. The issue of ease of developmentparticularly for what should be simple bits of code like the one we're talking about hereis one that we would like to do something about.

I don't have the date for the next CTP handy, but it's not immanentI wouldn't expect it before the end of October.

Cheers,

-Isaac

|||

Thanks you have been very helpful. We ended up using a CLR aggregate to solve our string problem. Just out of curiosity will we be able to use linq in the 08 CLR?

|||? LINQ isn't on the "short list" of .NET libraries tested and approved for direct loading in SQLCLR, if that's what you are asking. Cheers, Bob Beauchemin SQLskills ""akula"@.discussions.microsoft.com" <"=?UTF-8?B?YWt1bGE=?="@.discussions.microsoft.com> wrote in message news:8bd1a423-9adb-4b0b-9c22-0416c14805b4@.discussions.microsoft.com... Thanks you have been very helpful. We ended up using a CLR aggregate to solve our string problem. Just out of curiosity will we be able to use linq in the 08 CLR?