Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
billsack
Starting Member
35 Posts |
 Posted - 2007-10-04 : 05:42:23
|
Hello Experts!I have some data that counts the number of people waiting for a service (in week bands) each month:Site Month [0-1Weeks] [1-2Weeks] [2-3Weeks] ---- ----- ---------- ---------- ----------Site1 01/04/2006 5 4 8Site1 01/05/2006 5 1 2Site2 01/06/2006 3 0 3This is the way the data is stored in the database and I cannot change this for the time being.I need to run a query that displays the data differently. I need to show the waiting bands on the left and the months across the top ie:WaitPeriod 01/04/2006 01/05/2006 01/06/2006---------- ---------- ---------- ----------[0-1Weeks] 5 5 3[1-2Weeks] 4 1 0[2-3Weeks] 3 0 3I need to be able to do this for each site and for all sites combined.Can anyone help. It seems easier to Transpose the data in excel and do it that way but thats a fudge.Cheers folks |
|
|
pootle_flump
1064 Posts |
Posted - 2007-10-04 : 06:21:26
|
| The SQL solution is even more of a fudge. This is best handled by your front end. If you must do it in SQL then you need to get yourself familair with dynamic SQL. Based on one of your statements you know that the initial tables design is not good too. |
 |
|
|
billsack
Starting Member
35 Posts |
Posted - 2007-10-05 : 05:17:27
|
Ok thanks. I have resolved it by writing a query that passes the data into another table so that I can do the analysis. Thanks anyway. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-10-05 : 06:15:46
|
| sql server 2005 has the PIVOT functionality (look up PIVOT in BOL) but since you're on 2000 I guess your temporary table is as good a shot as any.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
Transpose Data