Crosstab Without Pivot
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?
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?
Best Answer
another way of doing is to use the CASE WHEN PIVOT_COL_1 = MAX(CASE WHEN COL = 'VALUE1' THEN COL2 END), PIVOT_COL_2 = MAX(CASE WHEN COL = 'VALUE2' THEN COL2 END)
Thank you for responding, your response has come in in two lines and the rest of it does not show so I am not sure what you have proposed. (Clicking on the 'more' link only results in Javascript errors)
