May
06

Columns to Rows without using UNION or UNPIVOT

Andrew Ing T-SQL Add comment

Problem

Table contains two or three columns that should be output as a single column over two or three rows.  Such as [Telephone1], [Telephone2], … columns in a table.

Use Table Values constructor in CROSS APPLY clause

Use the table values constructor, VALUES(), to create a derived table using columns from the other table in the FROM clause.  Literal values can also be inserted. It is nicely flexible.   Best of all it is compact:

SELECT TOP 100 Source
	,TelNo
FROM dbo.ContactBase
CROSS APPLY (
	VALUES (
		[Telephone1]	,'Tel1'
		)
		,(
		Telephone2	,'Tel2'
		)
		,(
		Telephone3	,'Tel3'
		)
	) DT (TelNo, Source)
WHERE TElNo IS NOT NULL
ORDER BY createdon DESC

The UNION method requires three scans of the table to return the same results:

-- Equivalent query using UNION
SELECT TOP 100 Source
	,TelNo
FROM (
	SELECT TOP 100 'Tel1' AS Source
		,Telephone1 AS TelNo
		,CreatedOn
	FROM dbo.ContactBase
	WHERE Telephone1 IS NOT NULL
	ORDER BY CreatedOn DESC

	UNION ALL

	SELECT TOP 100 'Tel2'
		,Telephone2 AS TelNo
		,CreatedOn
	FROM dbo.ContactBase
	WHERE Telephone2 IS NOT NULL
	ORDER BY CreatedOn DESC

	UNION ALL

	SELECT TOP 100 'Tel3'
		,Telephone3 AS TelNo
		,CreatedOn
	FROM dbo.ContactBase
	WHERE Telephone3 IS NOT NULL
	ORDER BY CreatedOn DESC
	) DT
ORDER BY CreatedOn DESC

Efficiency

Does UNION ALL in the FROM clause avoid multiple table scans (suggested by this  Stack Exchange article)?    The following code still caused three scans:

SELECT TOP 100 Source
	,TelNo
FROM (
	SELECT TOP 100 Telephone1, 'Tel1', CreatedOn FROM dbo.ContactBase ORDER BY CreatedOn Desc UNION ALL
	SELECT TOP 100 Telephone2, 'Tel2', CreatedOn FROM dbo.ContactBase ORDER BY CreatedOn Desc UNION ALL
	SELECT TOP 100 Telephone3, 'Tel3', CreatedOn FROM dbo.ContactBase ORDER BY CreatedOn Desc 

	) DT (TelNo, Source, CreatedOn)
WHERE TelNo IS NOT NULL
ORDER BY CreatedOn DESC

It doesn’t seem that the TOP 100 clause is making a difference.  These are the two approaches simplified to return the entire table:

SELECT Source
	,TelNo
FROM dbo.ContactBase
CROSS APPLY (
	VALUES (
		[Telephone1]	,'Tel1'
		)
		,(
		Telephone2		,'Tel2'
		)
		,(
		Telephone3		,'Tel3'
		)
	) V(TelNo, Source)

print 'methond 2'

SELECT Source
	,TelNo
FROM (
	SELECT Telephone1, 'Tel1', CreatedOn FROM dbo.ContactBase UNION ALL
	SELECT Telephone2, 'Tel2', CreatedOn FROM dbo.ContactBase UNION ALL
	SELECT Telephone3, 'Tel3', CreatedOn FROM dbo.ContactBase   

	) DT (TelNo, Source, CreatedOn)

The query plans are still very distinct (even if the timings are not so very different):

QueryPlans-UnpivotUsingVALUES

Limitations

Table Value constructor introduced version 2008.

References

The article that suggested this is-there-a-better-option-than-union-all-for-multiple-selects-from-the-same-row

Similar approach for aggregating across multiple columns: use-values-clause-to-get-the-maximum-value-from-some-columns

 

May
02

SSIS Export to a Named Worksheet in Excel

Andrew Ing SSIS Add comment

Exporting data to Excel, but to named spreadsheet(s).  These notes are taken from an exercise using SSIS 2008R2.

  • Create the Data Flow and export data (in required format) to an Excel Destination (creating an Excel Connection Manager in the process).  Use this to extract the Sheet creation SQL.
    • In Excel Destination Editor select “Table or view” as the Data access mode
    • Click the New button and copy the “CREATE TABLE `Excel Destination` ( …”  code.    The backticks are legitimate.
  • Edit the Excel Connection Manager, set DelayValidation to False.
  • Create some string variables:
    • ExcelSheetName
    • SheetCreationStatement
  • In a script, generate the ExcelSheetName and assign the “CREATE TABLE…” code copied above to the SheetCreationStatement.   Replace “Excel Destination” (between the backticks) with your name for the worksheet.
  • Create an Execute SQL Task. Set ConnectionType = EXCEL, Connection = (your Excel Connection Manager), SQLSourceType = Variable, SourceVariable = User::SheetCreationStatement.     This task will create the worksheets.
  • Amend the Excel Destination
    • Set Data access mode to “Table name or view name variable”
    • Select User::ExcelSheetName for the Variable name.

Difficulties

  • Excel doesn’t have a large palette of types: most columns (in the CREATE TABLE statement) are defined as Long Text, Double or Long.  I found that all varchar columns had to be converted to unicode text stream (DT_NTEXT).
  • Set ValidateExternalMetadata to False for the Excel Destination. (the workbook will be blank to start with).
  • I got this Warning in the Create Worksheet Task – but despite this, the worksheet was created and the job ran satisfactorily
    • Warning: 0x0 at Create Worksheet SQL Task: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.  [warning appears twice per operation]
  • During testing you’ll see this Error if a worksheet with the name you’re trying to create already exists:
    • Error: 0xC002F210 at Create Worksheet SQL Task, Execute SQL Task: Executing the query “CREATE TABLE `2012-10-02`

Limitations

  • Unsupported characters in worksheet names are changed to _.  That’s OK, but also some supported characters – such as – (hyphen) are also changed to _.  A limitation of CREATE TABLE  statement?

References

Original pattern provided by these articles