0

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?

atatko
  • 461
  • 6
  • 14
  • 1
    You're talking about trying to pivot with a dynamic number of columns. See: [http://www.sommarskog.se/dynamic_sql.html#Crosstab](http://www.sommarskog.se/dynamic_sql.html#Crosstab) for some hints. – Joe Stefanelli Apr 18 '11 at 20:19

4 Answers4

0

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.

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • In this example, there should be an entry for each day, whenever they have the value of "name". So if today, they check c, and c has 14 entries, the corresponding line in the db would be c 4-18-11 14. So it's not possible to determine how many dates will be available. – atatko Apr 18 '11 at 20:18
  • @atatko -- yes exactly -- was being called to a meeting in the middle of answering. – Hogan Apr 18 '11 at 21:04
  • I've been attempting to use dynamic sql for this, I think this should work. I'll look into PIVOT as well. Thanks for your help. – atatko Apr 19 '11 at 12:52
0

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.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
0

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

Community
  • 1
  • 1
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
-1

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

Beth
  • 9,531
  • 1
  • 24
  • 43
  • -1 - How is this an answer? The question is not "What other ways could this be done?" The question is how to do it in TSQL. – Hogan Apr 18 '11 at 21:13
  • the question was 'Is there a better way to do this?' – Beth Apr 18 '11 at 22:08
  • Sure, but using an external application is not a better way, by any reckoning. It is simply a different way. – Hogan Apr 19 '11 at 10:37
  • it's better if it's easier than struggling with t-sql's pivot statement and produces the same result. Access's crosstab wizard and Excel's pivot functions are designed to be easier to use than t-sql. – Beth Apr 19 '11 at 14:47
  • No details are given but imagine if this is being implemented in an enterprise environment -- how does the data leave the sql server and move to the desktop application and then move back? Your solution is only useful if problem is constrained to an ad-hoc report -- in any other use case I can think of it is the wrong way to solve the problem. While you may struggle with PIVOT and dynamic SQL I would hope this is not a design criteria for a project -- including ones you are working on. – Hogan Apr 19 '11 at 18:36
  • what I've done is use an SSIS package to copy the data from an Access crosstab query to a SQL server. It's much easier to maintain. – Beth Apr 19 '11 at 20:16