Convert table using DTS

[tolo]

Hi,
how to:
using dts convert row in table to column.
i have table:
col1,col2
b1 , 1
b2 , 2
b1 , 4
b3 , 3
b2 , 5
and i want using dts convert/rewrite this table to another table:
b1,b2,b3
1,null,null
null,2,null
4,null,null
null,null,3
null,5,null

[Simon Hayes]

Re: Convert table using DTS

"tolo" <ultrapack@ultr apack.pl> wrote in message news:<4120536f$ 1@news.home.net .pl>...[color=blue]
> Hi,
> how to:
> using dts convert row in table to column.
> i have table:
> col1,col2
> b1 , 1
> b2 , 2
> b1 , 4
> b3 , 3
> b2 , 5
> and i want using dts convert/rewrite this table to another table:
> b1,b2,b3
> 1,null,null
> null,2,null
> 4,null,null
> null,null,3
> null,5,null[/color]

The easiest way is probably to use a TSQL query:

select
case when col1 = 'b1' then col2 else NULL end as 'b1',
case when col1 = 'b2' then col2 else NULL end as 'b2',
case when col1 = 'b3' then col2 else NULL end as 'b3'
from
dbo.MyTable


You could use this either in an Execute SQL task, or as the source for
a Transform Data task. If both databases are on the same server, or on
different servers on a well-connected network, then you could also do
a simple INSERT...SELECT ...

Simon