Adding columns dynamically to a View or return from Stored Procedure

I've found a lot of bits and pieces of this, but I can't put the together. This is basically the idea of the table where name is a varchar, date is a datetime, and 开发者_如何学运维number is an int

Name | Date | Number
A     1-2-11  15
B     1-2-11   8
A     1-1-11   5

I'd like to create a view that looks like this

Name | 1-2-11 | 1-1-11
A      15       5
B       8

At first I was using a temp table, and appending each date row to it. I read on another forum that way was a major resource hog. Is that true? Is there a better way to do this?

---

I would combine dynamic SQL with a pivot as I mentioned in this answer.

---

You want to look into "cross-tab" or "pivot" statements. In SQL Server 2005 and up, its PIVOT, but syntax varries between platform.

This is a very complex subject, particuarly since you want to add columns to a view as your data grows over time. Besides your platform's documentation, check out the myriad other SO posts on the subject.

---

If the date column is a known set then you can use pivot in some cases.

It is often faster to use dynamic sql BUT this can be very dangerous so be wary.

To really know what the best solution is for your problem we would need some more information -- how much data -- how much variation is expected in the different columns, etc.

However, it is true, both PIVOT and Dynamic SQL will be faster than a temp table.

---

I would do it with Access or Excel instead of T-SQL.