DECLARE @y AS INT
DECLARE @m AS INT

SET @y = 2013
SET @m = 1

SELECT dbo.DateSerial(@y, @m, [Day]) AS pDate, [store], [amount]

FROM
	(
	SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=Z:\POS\XLS\01.xls;HDR=YES','SELECT * FROM [102$]') WHERE [??] IS NOT NULL
	) AS pvt

UNPIVOT
	(
	amount FOR store IN ([Bade], [Longtan])
	) AS unpvt

ORDER BY [Day]

DECLARE @query NVARCHAR(4000)
DECLARE @cols NVARCHAR(2000)

SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + Store.Store
                        FROM    GenshenHR.dbo.Store
						INNER JOIN (SELECT DISTINCT StoreID FROM CTT WHERE YEAR([date]) = @year AND MONTH([date]) = @month) AS aStores
						ON Store.StoreID = aStores.StoreID
                        ORDER BY '],[' + Store.Store
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'


SET @query = 'SELECT [d], [dw], '+ @cols +'
.......

[Store1],[Store2],[Store3],[Store4],[Store5],etc etc etc

SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=Z:\POS\XLS\01.xls;HDR=NO','SELECT * FROM [102$B1:Z1]')

DECLARE @SQL varchar(2000)
SET @SQL='SELECT *
FROM TableName t
UNPIVOT(ValueField FOR HeaderField IN(' + @cols + '))u'
EXEC(@SQL)

quote:

---

Originally posted by visakh16
you can use the list to UNPIVOT data in same way
something like

DECLARE @SQL varchar(2000)
SET @SQL='SELECT *
FROM TableName t
UNPIVOT(ValueField FOR HeaderField IN(' + @cols + '))u'
EXEC(@SQL)

---

quote:

---

Originally posted by ryan.gillies

quote:

---

Originally posted by visakh16
you can use the list to UNPIVOT data in same way
something like

DECLARE @SQL varchar(2000)
SET @SQL='SELECT *
FROM TableName t
UNPIVOT(ValueField FOR HeaderField IN(' + @cols + '))u'
EXEC(@SQL)

---

That's the obvious part, the difficult part is how to generate @cols in the first place.

In my previous example I pull all of the distinct records from one column in a table and stuff them together. This time I need to take the names of all of the columns from a header row within an xls file.

At this point I'm thinking its only possible with powershell, but does anyone else think otherwise...?

---

quote:

---

Originally posted by visakh16
you can use OPENROWSET based query itself for that

---

quote:

---

Originally posted by ryan.gillies
Doing the following will return a single row containing the names of each column header that I suppose I could concatenate somehow, but again I'm now stuck with a dynamically changing set of columns from F1 through to Fn

SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=Z:\POS\XLS\01.xls;HDR=NO','SELECT * FROM [102$B1:Z1]')

---

SELECT *  INTO TestTable FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=Z:\POS\XLS\01.xls;HDR=YES','SELECT * FROM [102$B1:Z1]')

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TestTable'

$sqlHDR = "SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + $dir + $m + ".xls;HDR=NO','SELECT * FROM [" + $y + "`$B1:Z1]')"
$doHDR = invoke-sqlcmd -query $sqlHDR -ServerInstance $sqlserver -Database $db
		
foreach($prop in $doHDR.psobject.properties | ? {$_.name.StartsWith("F")} | select value) #Get column headers from XLS
{
$hdr = $hdr + "[" + $prop.Value + "], "
}
$hdr = $hdr -replace "], $", "]"

quote:

---

Originally posted by ryan.gillies

Sorry visakh16, that was a bit too harsh of me, its been a long day and that wasn't the best way for me to let off steam; so my apologies.

I couldn't see where you were going with the OPENROWSET as I couldn't find anyway to read the schema, but you're correct in that creating tables would allow me to do so. However as I'm working with hundreds of xls files with multiple sheets in each the amount of tables created and dropped seems like an unnecessary overhead to me. In the end I went for the following Powershell solution:

$sqlHDR = "SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + $dir + $m + ".xls;HDR=NO','SELECT * FROM [" + $y + "`$B1:Z1]')"
$doHDR = invoke-sqlcmd -query $sqlHDR -ServerInstance $sqlserver -Database $db
		
foreach($prop in $doHDR.psobject.properties | ? {$_.name.StartsWith("F")} | select value) #Get column headers from XLS
{
$hdr = $hdr + "[" + $prop.Value + "], "
}
$hdr = $hdr -replace "], $", "]"

This loops through all the properties of the dataset, filtering it down to those beginning with F (as that's the default column heading for an excel spreadsheet with no HDR) and then mashes all of them together into a string to use in my UNPIVOT statement.

---