XML Crosstabs using XSLT

XML Crosstabs using XSLT

Creating cross tab queries and pivot tables in SQL

Creating cross tab queries and pivot tables in SQL (simple-talk.com)
For those times when you absolutely, positively got to perform a cross tab query in SQL, Keith Fletcher's T-SQL stored procedure will allow you to do it "on the fly". You can add it to your database and start cross tabbing immediately, without any further setup or... Read more...

CODE DOWNLOAD

simple pivot table in TSQL (SQL Server 2000)

read original post: Pivot in mssql2000

select
   t.myid as furnisher,
   t1.myprice as price1_year,
   t2.myprice price2_year from
(select distinct myid from mytable) t
join (select myid,myprice,rownum = (select count(*) from mytable t2 where t2.myid = t1.myid and t2.id <= t1.id) from mytable t1) t1 on
   t.myid = t1.myid and t1.rownum = 1
join (select myid, myprice, rownum = (select count(*) from mytable t2 where t2.myid = t1.myid and t2.id <= t1.id) from mytable t1) t2 on
   t.myid = t2.myid and t2.rownum = 2
order by 1 

dynamic crosstab / pivot tables in TSQL

read article:
Dynamic Cross-Tabs/Pivot Tables (SQLTeam.com)

dynamic crosstab queries in TSQL (SQLMag.com)

read the article:
Dynamic Crosstab Queries (SQLMag.com)

PIVOT SQL Server 2005 Syntax...

..., Using pivot table in ms access, Sql Server Pivot Table, what is a pivot table, pivot table tutorial

read the whole article:
PIVOT SQL Server 2005 Syntax, Using pivot table in ms access, Sql Server Pivot Table, what is a pivot table, pivot table tutorial

dynamic crosstab/pivot tables

Dynamic Cross-Tabs/Pivot Tables - SQLTeam.com
... a feature of MS Access is the TRANSFORM statement, used to create cross-tabs/pivot tables. It does all of the work of dynamically generating the cross-tabulation and the summary calculations. T-SQL unfortunately doesn't have this statement, so you're stuck using complicated SQL commands, expensive 3rd party products, or exotic OLAP to make pivot tables    ...or you can use the following procedure to dynamically create them!

CREATE PROCEDURE sp_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

usage:

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

examples:

pubs database:

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

EXECUTE sp_crosstab 'select pub_name, count(qty) as orders, sum(qty) as total 
from sales inner join titles on (sales.title_id=titles.title_id) 
right join publishers on (publishers.pub_id=titles.pub_id) 
group by pub_name', 'sum(qty)','type','titles'

Northwind database:

EXECUTE sp_crosstab 'SELECT LastName FROM Employees INNER JOIN Orders 
ON (Employees.EmployeeID=Orders.EmployeeID) 
GROUP BY LastName', 'count(lastname)', 'Year(OrderDate)', 'Orders'

Technorati Tags: crosstab

.

del.icio.us Tags: crosstab

SQL Server 2005 - PIVOT() sample for syslog priority code

1.)

select IpAddressSource,[1],[2],[3],[4],[5],[6],[7] from (
   select IpAddressSource,PriorityCode,count(*) N from sll1
   where datetimelocal >= '2008-01-01'
   group by ipaddresssource,prioritycode) a
pivot (sum(n)
for PriorityCode in ([1],[2],[3],[4],[5],[6],[7])) as pvt

2.)

select IpAddressSource,[1],[2],[3],[4],[5],[6],[7] from (
   select IpAddressSource,PriorityCodefrom sll1
   where datetimelocal >= '2008-01-01') a
pivot ( count(PriorityCode)
for PriorityCode in ([1],[2],[3],[4],[5],[6],[7]) ) as pvt