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.

Multiple Client Database Model

Does anyone have a sample of what a database would look like that would support multiple clients in a single ASP.net application?

Thank you,

Why would this be any different from any other database?
A database is meant to be shared amongst multiple clients. That'sone of the main reasons for using databases instead of storing data inflat files or other application-based structures. Databasessupport transactions and locking that keep clients from destroying eachothers' work. Perhaps I'm misunderstanding your question,though? Are you looking for an example of a specific type ofdatabase?

|||

Really what I was hoping was an example of a database that would be used on a web host that could maintain more than one client using it. For example two or more companies using the same hosted database. What is done to the data tables to allow for the separation of the companies and their clients, etc...

I hope this explains it better.

Thank you,

|||Okay, that makes a lot more sense
This can be achieved using what's known as "row-based security". See this article:
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
Note that there are some serious caveats in a row-based securityscheme, and if you really need heavy security it's probably better todo a seperate database per client. Steve Kass (SQL Server MVP)has posted some interesting ways he's found to hack row-based securityschemes -- unfortunately, the SQL Server engine doesn't realize you'redoing row-based security so it doesn't know not to show certain data inerrors/etc if you give it the right inputs.

|||Thank you, I will check it out.