CSV – row values concatenation (FOR XML PATH) and exporting column values to a MS Excel file (Microsoft.Jet.OLEDB)

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

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