Hi There,

Nice article, I will look at using this in future

previously, I have used the ACE driver to access Excel in the past with pivots to switch the data between rows and columns, the benefit of using the ACE driver is that you can link the queries etc to other SQL statements. also you only need to install the ACE driver on the server and not the Excel runtime - an element that many of the server admins that I have worked with didn't want on their servers.

Also with the ACE or OLEDB jet providers, you can specify specific cells and tabs within a spreadsheet.

below is my query that returns the same result as the one the article

select a.[Customer Name],

a.[Address],

a.[Sales Rep],

b.[Order Number],

b.[Order Date]

from (

select [Customer Name],

[Address],

[Sales Rep]

from (SELECT *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=No;Database=C:\Projects\KPI Demo\IT 2009 Tester.xls;IMEX=1',

'SELECT * FROM [Department Details$A3:B5]')) as sourcetable

PIVOT

(MAX(F2)

for F1 in ([Customer Name],[Address],[Sales Rep])

) as PivotTable) a

cross join (

select null as [Customer Name],

[Order Number],

[Order Date]

from (SELECT *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=No;Database=C:\Projects\KPI Demo\IT 2009 Tester.xls;IMEX=1',

'SELECT * FROM [Department Details$D3:E4]')) as sourcetable

PIVOT

(MAX(F2)

for f1 in ([Order Number],[Order Date])

) as PivotTable) b

Regards

Warren