by Richard A. DeVenezia, Back to HomeSend Feedback
Tranpose your vertical based categorical data into a different shape.
CREATE PROCEDURE dbo.TRANSPOSE (
@DATA NVARCHAR(MAX) -- Data to pivot
, @BY NVARCHAR(MAX) -- Columns to pivot about
, @ID NVARCHAR(MAX) -- Column expression for forming values that become column names
, @VAR NVARCHAR(MAX) -- Column expression for values that pivot
, @AGG NVARCHAR(MAX) = 'MIN' -- Aggregating function
, @ID_ORDER_BY NVARCHAR(MAX) = '' -- Clause for ordering the ID value
, @OUTVIEW NVARCHAR(MAX) = '' -- Name of output VIEW object.
, @OUTTABLE NVARCHAR(MAX) = '' -- Name of output TABLE object.
)
AS BEGIN
DECLARE @COLUMNS NVARCHAR(MAX) = '' -- Variable to hold VAR column values that will become column names
DECLARE @ID_VALUES NVARCHAR(MAX) = '' -- Variable to hold unique id expressions to be used in PIVOT clause
DECLARE @SQL_PIVOT NVARCHAR(MAX) = '' -- Variable to hold the fully realized pivoting SQL statement
DECLARE @SQL_IDS NVARCHAR(MAX) = '' -- Variable to hold the SQL statement that computes the idvalues
DECLARE @ID_ORDER NVARCHAR(MAX) = ''
DECLARE @ID_VALUES_SQL NVARCHAR(MAX) = '' -- Variable to hold SQL that selects the id values
IF LEN(@OUTVIEW) > 0 AND LEN(@OUTTABLE) > 0 BEGIN
PRINT 'ERROR: SPECIFY EITHER @OUTVIEW= OR @OUTTABLE=, NOT BOTH.'
RETURN
END
PRINT 'NOTE: TRANSPOSE STARTED @ ' + CONVERT(NVARCHAR(30), GETDATE(), 126)
IF LEN(@ID_ORDER_BY) = 0 BEGIN
SET @ID_ORDER = 'DENSE_RANK() OVER (ORDER BY ' + @ID + ')'
END
ELSE BEGIN
SET @ID_ORDER = 'DENSE_RANK() OVER (ORDER BY ' + @ID_ORDER_BY + ')'
END
DECLARE @LIST NVARCHAR(MAX) = ''
SET @ID_VALUES_SQL =
'SELECT @LIST = @LIST'
+ ' + '',['' + _ID_VALUE + '']'''
+ ' FROM'
+ ' (SELECT TOP 100 PERCENT _ID_VALUE, _ID_ORDER' -- TOP 100 PERCENT is needed so that order by clause can be applied
+ ' FROM ('
+ ' SELECT DISTINCT ' + @ID + ' AS _ID_VALUE, ' + @ID_ORDER + ' AS _ID_ORDER'
+ ' FROM ' + @DATA + ' _DATA'
+ ') DISTINCT_ID_VALUES '
+ ' WHERE _ID_VALUE IS NOT NULL'
+ ' ) ID_VALUES_ALIAS'
+ ' ORDER BY _ID_ORDER'
PRINT 'NOTE: @ID_VALUES_SQL = ' + @ID_VALUES_SQL
EXEC sp_executesql @ID_VALUES_SQL, N'@LIST NVARCHAR(MAX) OUT', @ID_VALUES OUT
PRINT 'NOTE: ID_VALUES=' + @ID_VALUES
PRINT 'NOTE: FOR DATA=' + @DATA
IF LEN(@ID_VALUES) = 0 BEGIN
PRINT 'ERROR: There were no ID values.'
RETURN
END
--Compute PIVOT query statement here
--The @AGG function (default MIN()) is applied over contributing values
--NOTE: MIN() works on either character or numeric, AVG will not
IF LEN(@OUTVIEW) > 0 BEGIN
IF OBJECT_ID (@OUTVIEW, N'V') IS NOT NULL BEGIN
EXEC (N'DROP VIEW ' + @OUTVIEW)
PRINT 'NOTE: REPLACING VIEW ' + @OUTVIEW
END
ELSE BEGIN
PRINT 'NOTE: CREATING VIEW ' + @OUTVIEW
END
SET @SQL_PIVOT = 'CREATE VIEW ' + @OUTVIEW + ' AS'
END
IF LEN(@OUTTABLE) > 0 BEGIN
IF OBJECT_ID (@OUTVIEW, N'T') IS NOT NULL BEGIN
EXEC (N'DROP VIEW ' + @OUTVIEW)
PRINT 'NOTE: REPLACING TABLE ' +@OUTTABLE
END
ELSE BEGIN
PRINT 'NOTE: CREATING TABLE ' + @OUTTABLE
END
SET @SQL_PIVOT = 'CREATE TABLE ' + @OUTTABLE + ' AS'
END
SET @SQL_PIVOT = @SQL_PIVOT
+ CHAR(10)
+ ' SELECT ' + @BY + CHAR(10) + @ID_VALUES -- ID_VALUES already has a leading comma
+ CHAR(10)
+ ' FROM (SELECT ' + @BY + ',' + @ID + ',' + @VAR
+ CHAR(10)
+ ' FROM' + @DATA + ' DATA_ALIAS ) PIVOT_SELECT'
+ CHAR(10)
+ ' PIVOT ('
+ CHAR(10)
+ ' MIN (' + @VAR + ') FOR '
+ CHAR(10)
+ @ID + ' IN (' + SUBSTRING(@ID_VALUES,2,32767) + ')'
+ CHAR(10)
+ ' ) PIVOT_ALIAS'
PRINT 'NOTE: @SQL_PIVOT=' + @SQL_PIVOT
--Execute the computed PIVOT query
EXEC (@SQL_PIVOT)
/*
-- Test example
create table #x (name char(5), year int, average int)
insert into #x values ('AAA', 8,90)
insert into #x values ('AAA', 9,91)
insert into #x values ('AAA', 10,92)
insert into #x values ('AAA', 11,93)
insert into #x values ('AAA', 12,94)
insert into #x values ('AAA', null,99)
insert into #x values ('BBB', 11,92)
insert into #x values ('BBB', 7,100)
select * from #x
exec dbo.TRANSPOSE @DATA='(select *, cast(year as varchar(4)) as yearstr from #x)', @BY='name', @ID='yearstr', @VAR='average', @ID_ORDER_BY='year'
go
-- A master detail example
CREATE PROCEDURE dbo.CREATE_md_pivot_view
AS BEGIN
DECLARE @DATA_QUERY_STRING NVARCHAR(MAX) =
'( select master_id'
+', master_field1'
+', master_field2'
+', master_field3'
+', master_field4'
+', detail_field1'
+', detail_field2'
+' from dbo.master_table M join dbo.detail_table D on M.master_id=D.master_id'
+')'
EXEC dbo.TRANSPOSE
@DATA = @DATA_QUERY_STRING
, @BY = 'master_id,master_field1,master_field2,master_field3,master_field4'
, @ID = 'detail_field1'
, @VAR = 'detail_field2'
, @ID_ORDER_BY = '(select detail_field1_sequence from dbo.detail_field1_order_lookup INNER_ALIAS where INNER_ALIAS.detail_field1=_DATA.detail_field1)'
, @OUTVIEW = 'dbo.md_pivot_view'
END
EXEC dbo.CREATE_md_pivot_view
*/
END
Copyright 2017 Richard A. DeVenezia