Another Dynamic Cross-Tabs/Pivot Tables in MS-SQL

First off, before going any further make sure you have read the hall of fame SQLTeam article by Rob Volk on generating crosstab results using a flexible, dynamic stored procedure that has been viewed over 100,000 times! This entire concept and pretty much all of the ideas I've had regarding this topic and this techinique in general are all due to Rob's great work and his very clever stored procedure.
It must be crosstab season or something, because lately I've been getting quite a few emails and comments about an alternative stored procedure that I've posted in the comments to that article that has been helping quite a few users. To potentially help others out there with this common request (which I still feel should be mostly done at the presentation layer, but I suppose it's not always possible) here's a quick recap/reprint of my adaptation of Rob's excellent idea and some notes.
The main difference between Rob's original stored procedure and mine are that
  • You can order by pivot Column
  • It works fine for multi-users (no global temp tables)
  • You can condition by pivot column

Note that if you read the comments from the article, you'll see lots of modifications and adaptations of Rob's original that do address some of these issues, but I am proud of the brevity and flexibility of my code and I feel that it works well and is easily modified (see the end of this article for one idea). Depending on your needs, it may be useful to incorporate some of the other ideas presented in the article and the comments, so be sure to do some research if necessary or if you are interested in learning more.
First, here's the code for the procedure:



ALTER PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@Where varchar(100),
@OrderByColumn varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
IF @OrderByColumn<>''
BEGIN
SET @OrderByColumn = ' ORDER BY ' + @OrderByColumn
END
IF @Where<>''
BEGIN
SET @Where = ' AND ' + @Where
END
EXEC ('SELECT ' + @pivot + ' AS pivot1 INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ('
+ @pivot + ' Is Not Null OR ' + @pivot + '<>'''')' + @Where + @OrderByColumn)
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot1'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot1) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot1) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
SET ANSI_WARNINGS ON


PROPERTY:

EXECUTE crosstab
select statement,
summary calculation,
pivot column,
pivot table name,
pivot column condition,
pivot column order by

(1).The SELECT statement can be anything, as long as it has proper syntax and includes a GROUP BY clause. You can use JOINs, but if you use table aliases you should include the alias in the summary calculation expression (2).

(2).The summary calculation must have an aggregate function like SUM(), AVG(), MIN(), MAX(), etc. You'd have to modify the code if you want to use DISTINCT with these functions. COUNT(*) won't work, you have to COUNT on a column.

(3).The pivot column must be in the table (4). You can use an expression for the pivot column (a+b, LEFT(FirstName,3), etc.) as long as it can be derived from the table listed in (4). A cross-tab heading will be created for each distinct value in the pivot colum/expression.

(4).This table can be any table in your database, or another database if you use the full naming syntax (database.owner.table). Tables in a linked server may also work, but I haven't tested this. It's possible that a derived table (nested SELECT) can work, but I haven't tested this either. You would need to enclose the SELECT statement in parentheses, and use a table alias outside these parentheses, like this: '(SELECT LastName FROM myTable) AS Surnames'

(5).This parameter ordering by pivot column. For example, parameter(3) = "NAME" then maybe parameter(5) = "NAME DESC".

(6).This parameter is used to filter out Pivot column


Here's two you can run :
1.
EXECUTE crosstab
'SELECT MONTH(DATE_) AS MONTHS FROM LG_208_01_STLINE STL WITH (NOLOCK)
INNER JOIN LG_208_CLCARD CL WITH (NOLOCK) ON CL.LOGICALREF = STL.CLIENTREF
WHERE DEFINITION_ LIKE ''%SERKAN%'' GROUP BY MONTH(DATE_) ORDER BY MONTH(DATE_) ',
'SUM(TOTAL)',
'DEFINITION_ ',
'LG_208_CLCARD',
'DEFINITION_ LIKE ''%SERKAN%'' ',
''

2.
EXECUTE crosstab
'SELECT CL.CODE,CL.DEFINITION_ FROM LG_208_01_STLINE STL WITH (NOLOCK)
INNER JOIN LG_208_CLCARD CL WITH (NOLOCK) ON CL.LOGICALREF = STL.CLIENTREF
WHERE TRCODE=1 GROUP BY CODE,DEFINITION_ ORDER BY CODE,DEFINITION_ ',
'SUM(TOTAL)',
'MONTH(DATE_)',
'LG_208_01_STLINE',
'MONTH(DATE_) ASC'

Hiç yorum yok:

Visual Studio 2017'de Devexpress 17.2.5 Toolbox görünmüyor

Visual Studio 2017 Toolbox'ı üzerinde Developer Express componentlerini göremiyorsanız aşağıdaki komutu çalıştırmak işini görecektir. (...