WikiJuanan : Tablas Referencia Cruzada SQL

Esto es una version antigua de TablasReferenciaCruzadaSQL de 2007-03-19 11:22:27..

http://www.sqlteam.com/item.asp?ItemID=2955

1 2 3 4

EXECUTE crosstab SELECT statement, summary calculation, pivot column, table name

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(First Name?,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 Last Name? FROM myTable) AS Surnames'

I'll list some cross-tab settings and the results. Here's two you can run in the pubs database:

EXECUTE crosstab 'select title from titles inner join sales on (sales.title_id=titles.title_id)
group by title', 'sum(qty)','stor_id','stores'

CREATE PROCEDURE crosstab 

@select varchar(8000),

@sumfunc varchar(100), 

@pivot varchar(100), 

@table varchar(100) 

AS

DECLARE @sql varchar(8000), @delim varchar(1)

SET NOCOUNT ON

SET ANSI_WARNINGS OFF


EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 

+ @pivot + ' Is Not Null')


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='pivot'


SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + 

stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 

+ @delim + convert(varchar(100), pivot) + @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

WikiJuanan: TablasReferenciaCruzadaSQL ...