How to create a Cross Tab Query in SQL Server 1

[itmasterw]

In Access you can create a Cross tab query, which is basically a Pivot table in Excel. This si the code that Access generates fro one of my queries:

TRANSFORM Sum([Summary 3].[ESC ADV BAL]) AS [The Value]" & _
"SELECT [Summary 3].[INV GROUP3], Sum([Summary 3].[ESC ADV BAL]) AS [Total Of ESC ADV BAL]" & _
"From [Summary 3] GROUP BY [Summary 3].[INV GROUP3] PIVOT [Summary 3].[ESCROW DLQ STATUS]

Obviously they use special key words like "Transform" that you cannot use in standard SQL.
I was wondering how I can I create a Cross tab query in SQL Sever.

Thank You

ITM

 

[checkai]

I've used this SP..

--crosstabD 'select chargeacid from sgtVKProjectBudgetStaff inner join tblstaff on sgtVKProjectBudgetStaff.staffid = tblstaff.staffid  group by chargeacid','sum(hours)','tblstaff.forename','sgtVKProjectBudgetStaff left join tblstaff on sgtVKProjectBudgetStaff.staffid = tblstaff.staffid left join tblstaffType on sgtVKProjectBudgetStaff.staffTypeid = tblstaffType.staffTypeid'
--SELECT STATEMENT,SUMMARY CALCULATION,PIVOT COLUMN,TABLE NAME
CREATE PROCEDURE crosstabD
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(200), 
@table varchar(200) 
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

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[itmasterw]

I notice you a function in here called stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
Can you give me details about this Function.
THank YOu

ITM