Pass in a query and a column name (or a column list) and receive an unpivoted result set back. Specify either the columns to unpivot or the columns to leave unpivoted (i.e. unpivot all but the specified columns). Behind the scenes, this stored proc casts value columns to a common data type of your choosing, eliminating “the type of column ‘x’ conflicts with the type of other columns specified in the UNPIVOT list” errors. It can also be used as a SQL generator, outputting the dynamically-built query’s text instead of executing it.

Note: The usual caveats involved with using dynamic SQL apply. While well-suited for administrative and data migration tasks, in production code you may be better off using a hard-coded query for performance and security reasons.

Requirement: Microsoft SQL Server 2012 or later.

CREATE TYPE ColumnList AS TABLE
(                     
	name SYSNAME NOT NULL PRIMARY KEY
)
GO

-- Provided "as is" with no warranties of any kind. User assumes all risks of use.
CREATE PROCEDURE DynamicUnpivot
	@Query NVARCHAR(MAX),					-- Query producing the resultset to unpivot.
	@Column SYSNAME = NULL,					-- Name of single column. Combined with @ColumnList to control which columns are unpivoted.
	@ColumnList ColumnList READONLY,		-- List of column names. Combined with @Column to control which columns are unpivoted.
	@UnpivotSpecified BIT = 0,				-- Indicates whether the specified columns (@UnpivotSpecified = 1) or all other columns execpt 
											--	the specified (@UnpivotSpecified = 0) should be unpivoted.
	@ValueColumnName SYSNAME = 'Value',		-- Name of value-containing column in the output resultset.
	@ColumnColumnName SYSNAME = 'Column',	-- Name of column-name-containing column in the output resultset.
	@ValueColumnType NVARCHAR(20) = 'NVARCHAR(100)',	-- Data type of output value column. All source columns that are unpivoted are CAST to this data type.
	@OnlyOutputSql BIT = 0					-- Controls whether the unpivot query is executed and its resultset returned (@OnlyOutputSql = 0) 
											--	or not executed and its SQL outputted (@OnlyOutputSql = 1).
AS
BEGIN
SET NOCOUNT ON;


/*
	Combine both column name inputs into one table variable. 
	UNIONing protects from duplicates when @Column is also in @ColumnList
*/
DECLARE @Columns dbo.ColumnList

INSERT INTO @Columns
SELECT name FROM @ColumnList
UNION
SELECT @Column WHERE @Column IS NOT NULL


/*
	Build a list of all columns to be returned, indicating which should be unpivoted.

	If @UnpivotSpecified = 1, column names found in *both* the input column names and the
	query's result set will be unpivoted. All other columns in the result set will be 
	output without unpivoting. 
	
	@UnpivotSpecified = 0 inverts this behavior.
*/
DECLARE @QueryColumns TABLE (
	name sysname NOT NULL PRIMARY KEY, 
	column_ordinal INT NOT NULL,
	ShouldUnpivot BIT NOT NULL
);

INSERT @QueryColumns
SELECT 
	r.name, 
	column_ordinal, 
	ShouldUnpivot = CASE WHEN @UnpivotSpecified = 1 THEN 
		CASE WHEN c.name IS NOT NULL THEN 1 ELSE 0 END 
	ELSE
		CASE WHEN c.name IS NOT NULL THEN 0 ELSE 1 END 
	END
FROM sys.dm_exec_describe_first_result_set(@Query, NULL, 0) r
	LEFT JOIN @Columns c ON r.name = c.name
WHERE is_hidden = 0
	AND r.name IS NOT NULL
	AND column_ordinal IS NOT NULL


DECLARE @ColumnName SYSNAME,
	@First BIT = 1

/*
	Assemble cast & unpivot SQL.
*/
DECLARE @CastSql NVARCHAR(MAX) = '',
	@UnpivotSql NVARCHAR(MAX) = ''


DECLARE upvt CURSOR FAST_FORWARD FOR 
	SELECT name FROM @QueryColumns WHERE ShouldUnpivot = 1 ORDER BY column_ordinal

OPEN upvt

FETCH NEXT FROM upvt 
	INTO @ColumnName;

WHILE @@FETCH_STATUS = 0 BEGIN
	IF @First = 0 
	BEGIN
		SET @CastSql += ', '
		SET @UnpivotSql += ', '
	END

	SET @CastSql += QUOTENAME(@ColumnName) + ' = CAST(' + QUOTENAME(@ColumnName) + ' AS ' + @ValueColumnType + ')'
	SET @UnpivotSql += QUOTENAME(@ColumnName)

	IF @First = 1
		SET @First = 0

	FETCH NEXT FROM upvt
		INTO @ColumnName
END

CLOSE upvt
DEALLOCATE upvt


/*
	Assemble SQL for non-unpivoted columns.
*/
DECLARE @NoUnpivotSql NVARCHAR(MAX) = ''
SET @First = 1

DECLARE npvt CURSOR FAST_FORWARD FOR 
	SELECT name FROM @QueryColumns WHERE ShouldUnpivot = 0 ORDER BY column_ordinal

OPEN npvt

FETCH NEXT FROM npvt
	INTO @ColumnName

WHILE @@FETCH_STATUS = 0 BEGIN
	IF @First = 0 
		SET @NoUnpivotSql += ', '
		
	SET @NoUnpivotSql += QUOTENAME(@ColumnName)

	IF @First = 1
		SET @First = 0

	FETCH NEXT FROM npvt
		INTO @ColumnName
END;

CLOSE npvt
DEALLOCATE npvt


/*
	Build the final query.
*/
DECLARE @NewLine NVARCHAR(2) = CHAR(13) + CHAR(10),
	@Tab NVARCHAR(1) = CHAR(9)
DECLARE @OutputSql NVARCHAR(max) = 'SELECT *
FROM (
	SELECT 
		' + @NoUnpivotSql + ', 
		' + @CastSql + '
	FROM (
		' + @Query + '
		) innerData
) data
	UNPIVOT (' + QUOTENAME(@ValueColumnName) + ' FOR ' + QUOTENAME(@ColumnColumnName) + ' IN (' + @UnpivotSql + ')) upvt'

/*
	Produce output.
*/
SET NOCOUNT OFF

IF @OnlyOutputSql = 0
	EXEC sp_executesql @OutputSql
ELSE
	PRINT @OutputSql

END
GO

 Usage Example

EXEC DynamicUnpivot
	N'SELECT * FROM (VALUES (''Joe'',1,2), (''Frank'',3,4)) Data(Salesman, NumbSoldProdA, NumbSoldProdB)',
	@Column = 'Salesman'