question

scptech avatar image
scptech asked

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?
pivot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Squirrel avatar image
Squirrel answered
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)
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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)
0 Likes 0 ·
Show more comments
Ok, worked great.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.