I have a table with 3 columns, staffId, slotNo & apptSeq. CREATE TABLE [dbo].[appts]( [staffId] [varchar](15) NOT NULL, [slotNo] [int] NOT NULL, [apptSeq] [int] NULL ) ON [PRIMARY] There are 4,698 rows, one row for each of 87 staffIds and 54 slotNo combinations. staffId slotNo apptSeq --------------------------- aeason 1 570661
aeason 2 570661
aeason 3 570655
...... .. NULL aeason 54 59058
jtate 1 23128 jtate 2 NULL jtate 3 38544 ...... .. ..... jtate 54 31115 I want to show the staff across the top as columns, and the apptSeq for each staff / slot as such: slotNo aeason jtate .......... ------------------------------------------------- 1 570661 23128 2 570661 NULL 3 570655 38544 .... ...... ......
54 59058 31115 This the result will have 88 columns (slotNo + 87 unique staffIds), and 54 rows, one for each slot. I can do this with a pivot table, by dynamically generating the SQL for the pivot table, it works fine. I am looking for a way to do this without using pivot. While the pivot table runs fairly quickly on a development machine, it runs very slow on a production table (1 sec vs 36 secs). The development machine uses SQL Server 2005 express, with 1 CPU and 4 GB RAM. The production server uses SQL Server 2008 Enterprise, and has significantly 2 CPUs and 34 GB RAM, so I cannot tell why it takes so much longer on the production machine. I have even tried to create a new database on the production machine with just the one table used in the query, it still takes just as long. Any ideas?
