Skip to Content

reverse pivot transformation help

hi guys

I am trying to create multiple rows data in single row using reverse pivot transformation but somehow it is not working..

Sample data scripts:-

CREATE TABLE TEST (id INT,dt1 datetime, total int, new int, old int, status_1 varchar(50), no_ int)

INSERT INTO TEST VALUES (101,'2013-04-24 00:00:00.000',10, 7, 3, 'new with tags', 2)

INSERT INTO TEST VALUES (101,'2013-04-24 00:00:00.000',10, 7, 3, 'new without tags',4)

INSERT INTO TEST VALUES (102,'2013-11-07 00:00:00.000',15, 9, 6, 'new with tags', 5)

INSERT INTO TEST VALUES (102,'2013-11-27 00:00:00.000',15, 9, 6, 'new without tags', 2)

INSERT INTO TEST VALUES (102,'2013-11-27 00:00:00.000',15, 9, 6, 'return', 1)

INSERT INTO TEST VALUES (103,'2013-12-04 00:00:00.000',25, 15, 10, 'cancelled', 5)

select * from TEST

CREATE TABLE TEST_RESULT (id INT,dt1 datetime, total int, new int, old int, [new with tags] varchar(50), [new without tags] varchar(50), [return] varchar(50), [cancelled] varchar(50))

INSERT INTO TEST_RESULT VALUES (101,'2013-04-24 00:00:00.000',10, 7, 3, 2,4,0,0)

INSERT INTO TEST_RESULT VALUES (102,'2013-11-07 00:00:00.000',15, 9, 6, 5,2,1,0)

INSERT INTO TEST_RESULT VALUES (101,'2013-12-04 00:00:00.000',25, 15, 10, 0,0,0,5)

select * from TEST_RESULT.

Please find attached scrrenshot of dataflow using reverse pivot transformation... Please let me know what i am doing wrong..

Regards

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Nov 16, 2015 at 08:13 PM

    Definition from the SAP Data Services Reference Guide:

    "

    Axis Value: The value of the pivot axis column that represents a particular set of output columns. A set of Pivoted columns is generated for each axis value. There should be one Axis value for each unique value in the Pivot axis column.

    "

    Your Pivot axis column is status_1. You must specify its values ('new with tags', 'new without tags'...) as Axis values (and not 1,2.. like you've done).

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 17, 2015 at 05:31 AM

    Below doc will help

    How to convert rows to columns in BO Data Services

    The axis values are important and need to be specified as it is from the values of status_1 column. There are 4 axis values in your case.

    'new with tags'

    'new without tags

    returned

    cancelled

    'Regards

    Arun Sasi

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.