Task: to create row values concatenation, i.e. CSV (Comma Separated Values), by means of the FOR XML PATH construction, and to export database table column values to the MS Excel file using the Microsoft.Jet.OLEDB ad hoc method of connecting and accessing remote data by using OLE DB
Solution: see Source code 1 – 4
Solution: see Source code 1 – 4
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'ad hoc distributed queries', 1;
GO
RECONFIGURE;
GO
Source code 1: system configuration
USE [AdventureWorks]
GO
DECLARE @InputData AS TABLE
(
[ManagerCounter] INT,
[ManagerLoginID] NVARCHAR(256),
[EmployeeLoginID] NVARCHAR(256)
)
;WITH [Employees]
(
[ManagerCounter],
[ManagerID],
[LoginID]
)
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY [ManagerID] ORDER BY [ManagerID]),
[ManagerID],
[LoginID]
FROM [HumanResources].[Employee]
)
INSERT INTO @InputData
(
[ManagerCounter],
[ManagerLoginID],
[EmployeeLoginID]
)
SELECT [ManagerCounter],
[Managers].[LoginID],
[Employees].[LoginID]
FROM [HumanResources].[Employee] AS [Managers] INNER JOIN [Employees] ON
[Managers].[EmployeeID] = [Employees].[ManagerID]
ORDER BY [Managers].[LoginID]
/* temporary test */
SELECT [ManagerCounter] AS 'ManagersCounter',
[ManagerLoginID] AS 'Managers',
[EmployeeLoginID] AS 'Employees'
FROM @InputData
ORDER BY [ManagerLoginID]
Source code 2: input data preparation (ManagersCounter – number of employees per manager)
Figure 1: MS SSMS result of input data preparation (ManagersCounter - number of employees per manager)
SELECT [Managers].[ManagerLoginID] AS 'Manager',
(
SELECT
CASE
WHEN [Employees].[ManagerCounter] = 1
THEN [Employees].[EmployeeLoginID]
ELSE ',' + [Employees].[EmployeeLoginID]
END
FROM @InputData AS [Employees]
WHERE [Employees].[ManagerLoginID] = [Managers].[ManagerLoginID]
ORDER BY [Employees].[ManagerLoginID]
FOR XML PATH('')
) AS 'Employees'
FROM @InputData AS [Managers]
GROUP BY [Managers].[ManagerLoginID]
Source code 3: row values concatenation (converting to the CSV format) by means of FOR XML PATH
Figure 2: MS SSMS result of row values concatenation (converting to the CSV format) by means of FOR XML PATH
INSERT INTO OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Managers.xls;',
-- NOTE: the MS Excel file must already exist on the disk
-- together with the specified path and name!
'SELECT [Managers] FROM [Managers$]'
-- NOTE: the above-mentioned MS Excel file must have one
-- sheet named Managers, and the value in cell [0, 0],
-- i.e. the first row and column, must be
-- set to Managers (see Figure 5)!
)
SELECT [Managers].[ManagerLoginID]
FROM @InputData AS [Managers]
GROUP BY [Managers].[ManagerLoginID]
GO
Source code 4: exporting data (the column Managers) to the MS Excel file on the disk
Figure 3: MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk - error
Figure 4: MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk - solution for the above error
Figure 5: MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk
Additional references:
- Concatenating Row Values in Transact-SQL
by Anith Sen - Data truncated to 255 characters with Excel ODBC driver
by Microsoft - Execute SQLTask Error String or binary data would be truncated
by MSDN - Export to Excel
by SQL Team - OPENROWSET (Transact-SQL)
by MSDN - SQL Memory and External Data
by Nathon Dalton - SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet
by Pinal Dave - SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE
by Pinal Dave - SQL SERVER – Comma Separated Values (CSV) from Table Column
by Pinal Dave - SQL SERVER – Create a Comma Delimited List Using SELECT Clause From Table Column
by Pinal Dave
Placko's database KB 