Okay, I have a function call to extract lines of text out of SAP.
Number…Lines
12345…Hello this is
12345…a test of the
12345…emergency broadcast system
I think this will result in multiple rows of data being inserted in my table. I would like to know if there is a way to concatenate the Lines column into one field within Data Services before this gets into a table
I heard of Pivot but not sure how it works or how I can make it work since I’m new to ETL
Joealyche (BOB member since 2012-02-29)
One way of doing that would be to change your function call, your ABAP program so it does return the string as one long text.
With pivot, actually reverse pivot transform, you would add a gen_row_num_per_group(Query.NUMBER) so that your query does output the data like
Number…COUNTER…Lines
12345…1…Hello this is
12345…2…a test of the
12345…3…emergency broadcast system
This counter column is then the reverse pivot axis column with the values 1,2,3,… up to a maximum number of lines and behind the transform you concat the output column to one large string.
And then there are other ways of doing that, if you search for “stragg” you should find other postings for similar cases.
Werner Daehn 
(BOB member since 2004-12-17)
So in my Query I have the following :
Number
Function Call Z_AW_READ_TEXT
…Lines
…TDFormat varchar(2)
…TDLINE varchar(132)
I am very new to doing this sort of thing so where or how can I add in a Counter? I know how to add a column but nothing that helps in saying Counter will increment…
I will search our stragg and see if there are any helpful hints as well.
Thanks so much for your help
Joealyche (BOB member since 2012-02-29)
you rightclick the LINES, make it current and then unnest. In the downstream query you use the gen_row_num_per_group() function.
Werner Daehn (BOB member since 2004-12-17)
I am also starting to use the gen_row_num_per_group()
I have a query with a primary key(numberid) then I have the function call for the text(tdformat, tdlines) then I added a column called axis_value at the end of the table(right under the Tdlines)
In the reverse pivot I am using axis_value as the pivot axis column. Would the axis value contain the number of lines(so if I have about 12 lines) I need to add a row numbered up to 12?
I have no idea what value to put in the Column Prefix?
The Non-pivot columns is just the one other field: numberid.
Not sure what to put in the Pivoted columns section as there are no more fields??
toscajo (BOB member since 2002-09-04)
Yes, the pivot axis column is the 1…12 values.
The prefix column value does not matter, leave it as is. And once you configured the transform press F5 to see the impact. With that you can play around with the settings a bit.
Werner Daehn (BOB member since 2004-12-17)
Thanks Werner,
I was able to get the comments out but with some issues.
I kept getting data collision error messages. I kept adding to the Axis Value and actually changed the Duplicate Value impact to First Row instead of Abort.
The only other thing is that although I returned the text I only got the first row of information. The other lines are all values. Not sure if its because users are imbedding a carriage return whic his effecting this or not.
So I am 99% there, I just need to figure why I am only getting the first row of information than nothing. Thanks again for your help.
toscajo (BOB member since 2002-09-04)
Thanks, I too seem to have the exact same issue.
I am now able to get the first row of text but the need is to bring in all the rows and get them into one long column. I have a maximum of 10 rows and when I look at the temp table created. I see just one row per number and the first column filled in. The remaining columns are empty.
If I don’t use the reverse pivot transformation I get all the informaiton but get up to 10 rows per number which can get exceedinly larger in time.
I think I am on the right track but at a road block with this and it needs to work by end of day today 
Joealyche (BOB member since 2012-02-29)
Okay, I got rid of the reverse pivot and just called the read_text function as Werner described.
Now I am only returning the LAST line of text ???
Joealyche (BOB member since 2012-02-29)
Okay, this is drivin me insane now!
I got rid of the reverse pivot; since the gen…() does not work on TDLINES.
I ran the process and brought every single line into a table.
But not sure how to go about displaying the information. I have multiple rows but only want to show one row.
If I can figure out either:
Contexts within my report, or
concatenate all the rows before leaving Data Services I might be okay.
But I am too much of a young green hornette to get sophisicated with Data Services.
Joealyche (BOB member since 2012-02-29)
Okay, another “work around”
I was able to use the reverse pivot but had to bring in another Data Flow to do it.
Second One for the actual text description being used. Tired of these constan work arounds. Too many to keep track of and needless to say not included in any SAP book of value; don’t get me started on support 
Thanks again all .
Joealyche (BOB member since 2012-02-29)
Sounds as if I should enhance the wiki chapter about that. Can you point me into the right direction on what the problems were and what I can document better?
Werner Daehn (BOB member since 2004-12-17)
Not sure what DB you guys are on… I personally like dropping the data as is into a DB table, namely to be able to back-track any data anomalies… if you do so, you can simply use the coalesce function at report run time or create a function that you can then call within ETL.
Note that the example below is SQL Server specific but Oracle has similar functionality, also note the function below does not account for carriage returns or line breaks - it simple builds a long text.
Also the function below presents different options on how to call - but you get the picture.
Hurdle after this - How to display the long text via your UI - such as webi - just keep in mind formatting will be required.
CREATE
FUNCTION [dbo].[ReadProjectElementText](@InProjectElementId varchar(100), @InParseOption int )
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)
IF @InParseOption = 1
BEGIN
(SELECT @RETURN_STRING=COALESCE(@RETURN_STRING,’’) + TDLINE + ’ ’
FROM dbo.PROJECT_ELEMENT_LONG_TEXT
WHERE PROJ_ELEM_ID = CAST(@InProjectElementId AS INT)
AND TDLINE IS NOT NULL)
END
ELSE IF @InParseOption = 2
BEGIN
(SELECT @RETURN_STRING=COALESCE(@RETURN_STRING,’’) + TDLINE + ’ ’
FROM dbo.PROJECT_ELEMENT_LONG_TEXT, dbo.PROJECT_ELEMENT
WHERE dbo.PROJECT_ELEMENT_LONG_TEXT.PROJ_ELEM_ID = dbo.PROJECT_ELEMENT.PROJ_ELEM_ID
AND dbo.PROJECT_ELEMENT.DERIVED_SHORT_NAME = @InProjectElementId
AND TDLINE IS NOT NULL)
END
RETURN @RETURN_STRING;
END
SalH 
(BOB member since 2010-11-09)
In addition…
Example below replaces Tab, Line Feed and Carriage Return, ‘char(9)’, ‘char(10)’, ‘char(13)’ respectively, within each text line (in case of error in user entry) but adds a Carriage Return after each row.
At any rate - you see the ins and outs of using this method
IF @LevelofDetail = ‘PHASE’
SELECT @Agencies = COALESCE(@Agencies,’’) + A.Agencies + CHAR(13)
FROM (
SELECT
distinct REPLACE(REPLACE(REPLACE(A.description, CHAR(10), ’ '), CHAR(13), ’ '), CHAR(9), ’ ') AS Agencies
FROM
fmaxadmin.ae_b_loc_d A,
fmaxadmin.ae_s_bld_c B,
fmaxadmin.ae_p_phs_e C
WHERE
A.region_code = B.region_code
AND A.fac_id = B.fac_id
AND A.bldg = B.bldg
–AND A.loc_status = ‘ACTIVE’
AND A.region_code = C.region_code
AND A.fac_id = C.fac_id
AND A.bldg = C.bldg
AND C.proposal = @IDNumber
AND C.sort_code = @IDNumber2
) A
ELSE
SalH (BOB member since 2010-11-09)
I had a problem when using a Function to be added to the destination table. I could not unnest as it was greyed out. Trying to use Reverse Pivot would not work either as the counter was always set to 1 bringing in only one line where there were up to 30.
Created another query simply copying over the table. Lo and behold Unnest was there. Done
Saved output to a table.
Created another Data Flow accessing this table
Added the row count
Added the reverse pivot
Outputted to another table. Done
It works great.
Joealyche (BOB member since 2012-02-29)
Write a wrapper function in abap to concatenate strings? That was what i did.
aderici (BOB member since 2012-01-13)