Is it possible to write a database view that encompasses one-to-many relationships?

Question

So I'm not necessarily saying this is even a good idea if it were possible, since the schema of the view would be extremely volatile, but is there any way to represent a has-many relationship in a single view?

For example, let's say I have a customer that can have any number of addresses in the database. Is there any way to list out each column of each address with perhaps a number as a part of the alias (e.g., columns like Customer Id, Name, Address_Street_1, Address_Street_2, etc)?

Thanks!

Answer 1

Yes - use:

CREATE VIEW customer_addresses AS
    SELECT t.customer_id,
           t.customer_name,
           a1.street AS address_street_1,
           a2.street AS address_street_2
      FROM CUSTOMER t
 LEFT JOIN ADDRESS a1 ON a1.customer_id = t.customer_id
 LEFT JOIN ADDRESS a2 ON a2.customer_id = t.customer_id

If you provided more info, it'd be easier to give you a better answer. It's possible you're looking to pivot data (turn rows into columns).

Answer 2

Simply put, no. Not without dynamically recreating the view every time you want to use it at least, that is.

But, what you can do is predefine, say, 4 address columns in your view, then populate the first four results of your one-to-many relation into those columns. It's not quite the dynamic view you want, but it's also much more stable/usable in my opinion.

Answer 3

Not really - you really are doing a dynamic pivot. It's possible to use OPENROWSET to get to a dynamically generated query, but whether that's advisable, it's hard to say without seeing more about the business case.

First make a stored proc which does the dynamic pivot like I did on the StackExchange Data Explorer.

Basically, you generate dynamic SQL which builds the column list. This can only really be done in a stored proc. Which is fine for applciation calls.

But what about if you want to re-use that in a lot of different joins or ad hoc queries?

Then, have a look at this article: "Using SQL Servers OPENROWSET to break the rules"

You can now call your stored proc by looping back into the server and then getting the results into a rowset - this can be in a view!

The late Ken Henderson has some good examples of this in his excellent book: "The Guru's Guide to SQL Server Stored Procedures, XML, and HTML" (you got to love the little "Covers .NET!" on the cover which captures well the zeitgeist for 2002!).

He only covers the loopback part (with views and user-defined functions), the less verbose PIVOT syntax was not available until 2005, but PIVOTs can also be generated using a CASE statement as a characteristic function.

Obviously, this technique has caveats (I can't even do this on our production server).