Pivoting one source row into multiple destination rows 
Author Message
 Pivoting one source row into multiple destination rows

I'd like to implement a DTS package that can be triggered
by DTS within Enterprise Manager, that pivots one source
row into multiple destination rows.

Example:
Date       Units_ProductX Units_ProductY
01/01/2003             10             20

Should be transformed into a table like:
Date       Product Units
01/01/2003       X    10
01/01/2003       Y    20

Is it possible to implement this by using a Transform Data
Task or an ActiveX Script Task? And does anyone have some
examples?

Thanks in advance,




Sun, 25 Sep 2005 15:18:08 GMT
 Pivoting one source row into multiple destination rows

Hello, Frank!

The easiest way to implement this is to use two DataPump tasks.  One for the
X and One for the Y.   Should be very quick too.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

: Example:
: Date       Units_ProductX Units_ProductY
: 01/01/2003             10             20

: Should be transformed into a table like:
: Date       Product Units
: 01/01/2003       X    10
: 01/01/2003       Y    20

: Is it possible to implement this by using a Transform Data
: Task or an ActiveX Script Task? And does anyone have some
: examples?

: Thanks in advance,

      ---  Microsoft CDO for Windows 2000



Sun, 25 Sep 2005 15:46:59 GMT
 Pivoting one source row into multiple destination rows
Hello,

Thanks for the suggestion. But I'm looking for a more generic solution,
because in this case the number of products determines the number of
data pumps.

Another example of transforming one source row to multiple destionation
rows could be:
From_Date  To_Date    Production_Line  Capacity_Hour
01/01/2003 01/03/2003              PL1            20
01/04/2003 01/05/2003              PL1            25

Should be transformed to:
Date        Production_Line  Capacity_Hour
01/01/2003              PL1             20
01/02/2003              PL1             20
01/03/2003              PL1             20
01/04/2003              PL1             25
01/05/2003              PL1             25

I hope this example clarifies (better) the solution I'm looking for.

Frank van Zuilen

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 25 Sep 2005 17:33:29 GMT
 Pivoting one source row into multiple destination rows
Hello, Frank!

I cannot think of a generic enough solution for you here then.  In this
example you want to take the rows and for each date between the Start Date
and the End Date add entries to the database.  I think a "One size fits all"
solution will be a little tricky.

For this example though you could obviously use a cursor to do what you
want.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

: Thanks for the suggestion. But I'm looking for a more generic solution,
: because in this case the number of products determines the number of
: data pumps.

: Another example of transforming one source row to multiple destionation
: rows could be:
: From_Date  To_Date    Production_Line  Capacity_Hour
: 01/01/2003 01/03/2003              PL1            20
: 01/04/2003 01/05/2003              PL1            25

: Should be transformed to:
: Date        Production_Line  Capacity_Hour
: 01/01/2003              PL1             20
: 01/02/2003              PL1             20
: 01/03/2003              PL1             20
: 01/04/2003              PL1             25
: 01/05/2003              PL1             25

: I hope this example clarifies (better) the solution I'm looking for.

: Frank van Zuilen

      ---  AspNNTP 1.50 (ActionJackson.com)



Sun, 25 Sep 2005 18:33:44 GMT
 Pivoting one source row into multiple destination rows
Frank,
It always helps to have complete details of your problem and expected
solution, and the volunteers greatly appreciate getting all the info up
front, rather than seeing changes trickle in.

Where are your source rows coming from?
If it is SQL Server, it is possible to pivot your data using any number of
ways.
(stored proc and view to name two)
but then you would not even need DTS unless you are transferring the pivoted
data to another server.

If the source is Access, you could take advantage of the pivoting functions
built into access

If it is say, a text file, you could use an ActiveX script to pivot the data

From what you have said so far, In addition to pivoting your data, you also
need to create additional rows
(01/02/2003              PL1             20)
this could be a two step process, first creating the pivot, then creating
the "missing" data, but the ideal solution would require more information
from you first.

HS

| : From_Date  To_Date    Production_Line  Capacity_Hour
| : 01/01/2003 01/03/2003              PL1            20
| : 01/04/2003 01/05/2003              PL1            25
|
| : Should be transformed to:
| : Date        Production_Line  Capacity_Hour
| : 01/01/2003              PL1             20
| : 01/02/2003              PL1             20
| : 01/03/2003              PL1             20
| : 01/04/2003              PL1             25
| : 01/05/2003              PL1             25
|
| : I hope this example clarifies (better) the solution I'm looking for.
|



Sun, 25 Sep 2005 21:51:31 GMT
 Pivoting one source row into multiple destination rows
HJ,

First of all I'd like to apologize for my late response.
Holidays etc.

The source file in this case is a text file. So I need to
develop an ActiveX Task, according to your suggestion.
Could you provide me any example? And is creating an
AxtiveX Task the only solution, or could I use an
Transformation Task as well by adding some additional code
around DTSSource and DTSDestination statements?

Frank van Zuilen



Sat, 08 Oct 2005 16:46:12 GMT
 Pivoting one source row into multiple destination rows
Frank

If you are referring to my solution then you would create two datapump tasks
and one BULK INSERT task.

1.  BULK INSERT the whole text file into a scratch table (so you can use
TSQL to manipulate the data)
2.  Use DataPump task 1 to pump only the Y rows into your destination
3.  Use DataPump task 2 to pump only the X rows into your destination

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



Quote:
> HJ,

> First of all I'd like to apologize for my late response.
> Holidays etc.

> The source file in this case is a text file. So I need to
> develop an ActiveX Task, according to your suggestion.
> Could you provide me any example? And is creating an
> AxtiveX Task the only solution, or could I use an
> Transformation Task as well by adding some additional code
> around DTSSource and DTSDestination statements?

> Frank van Zuilen



Sat, 08 Oct 2005 16:56:04 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. One source row to multiple destination rows

2. DTS- multiple destination rows for each source row

3. Creating Multiple Destination Rows from a single source Row

4. multiple inserts in destination for each source row

5. Adding Multiple Rows while Transforming from Source to Destination

6. column in multiple rows to multiple columns in one row

7. column in multiple rows to multiple columns in one row

8. Multiple Target Rows from a Single Source Row

9. concatenate varchar field from multiple rows into one row as a group

10. Get values from multiple rows as attributes of one row

11. Combing multiple row data into one row in another table

12. SELECT multiple rows back as one row with many columns


 
Powered by phpBB® Forum Software