Multiple columns as the pivot key

Hello,

Here is a sample of the data that I am trying to pivot;

rec_id sequence field_name value

1 1 cat_nbr Granrier

1 1 cat_page pg 21

1 2 cat_nbr H&S

1 2 cat_page pg234

2 1 cat_nbr Ford

2 1 cat_page pg5

I need to pivot on rec_id and sequence to get an output like this:

rec_id sequence cat_nbr cat_page

1 1 Granrier pg21

1 2 H&S pg234

2 1 Ford pg5

All I seem to be able to get thoug is this:

rec_id sequence cat_nbr cat_page

1 1 Granrier

1 1 pg21

1 2 H&S

1 2 pg234

2 1 Ford pg5

It seems to me that the pivot transform can only pivot around one key value column. What am I missing?

Thanks.

This is an easy SQL statement -- no need for SSIS: (I'm assuming your table is named "table" -- change accordingly)

SELECT a.rec_id, a.sequence, a.cat_nbr, b.cat_page
FROM

(SELECT rec_id, sequence, value as cat_nbr
FROM table
WHERE field_name = 'cat_nbr') a,

(SELECT rec_id, sequence, value as cat_page
FROM table
WHERE field_name = 'cat_page') b

WHERE a.rec_id = b.rec_id
AND a.sequence = b.sequence|||you can also use a derived column and combine the various elements of the compound key into a single key field. i.e., rec_id + "_" + sequence.|||I was having the same problem. Add a Sort transform before the pivot in which you sort by the key columns. That should eliminate the duplicates.