asked on

SSiS How to transform multiple rows into different columns of one row

I have one DB with a table defined as follows:

ProductsInStock1
ProductID
Size
Qty

If we had product 1 available in small medium and large the three records would look like:

1 | Small | 92
1 | Medium | 33
1 | Large | 30

I need to transform this using SSIS to a table defined as:

ProductsInStock2
ProductID
QtySmall
QtyMedium
QtyLarge
QtyExtraLarge

For the same product above I would want a single row as follows:

1 | 92 | 33 | 30 | NULL

How can I transform the data as required?

David Todd

Hi,

Wouldn't this be best done as a pivot query?

use ExpertsExchange
go

if exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.ProductsInStock' )
		and type in( N'U' )
	)
	drop table dbo.ProductsInStock
;
go

if not exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.ProductsInStock' )
		and type in( N'U' )
	)
	create table dbo.ProductsInStock (
		ProductID int
		, size nvarchar( 20 )
		, Qty int
	) on [PRIMARY]
go

insert dbo.ProductsInStock 
	values( 1, 'Small', 92 )
	, ( 1, 'Medium', 33 )
	, ( 1, 'Large', 30 )
;

select *
from dbo.ProductsInStock
;

select 
	p.ProductID
	, Small
	, Medium
	, Large
	, ExtraLarge
from dbo.ProductsInStock s
pivot (
	sum( s.Qty )
	for s.size in ( [Small], [Medium], [Large], [ExtraLarge] )
	) p
order by
	p.ProductID
;

Open in new window

HTH
David

ASKER CERTIFIED SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

canuckconsulting

ASKER

Perfect...thanks!