Crosstab or Pivot table in SQL: How?

[rvr]

I would like to create a table that behaves much like a crosstab query(MS Access) or a Pivot table (MS Excel), using SQL. I use one table with grouped columns, and pivot on the contents of one of the columns to show totals of another column. The Access version look like this:<br>
<br>
TRANSFORM Sum([ColA]) AS [The Value]<br>
SELECT [Colb], [ColC], [Cold]<br>
FROM TableA<br>
GROUP BY [Colb], [ColC], [Cold]<br>
PIVOT [Cole];

 

[crk]

There is a pretty good explanation on how to use the CASE statement to write crosstabs in the Sams Teach Yourself Transact-SQL in 21 Days book by McEwan & Solomon.<br>
<br>
Here is an example of one I did:<br>
select case<br>
when HHC_Occupation_Cd = '1'<br>
then '1.Professional/Technical'<br>
when HHC_Occupation_Cd = '2'<br>
then '2.Administrative/Managerial'<br>
when HHC_Occupation_Cd = '3'<br>
then '3.Sales/Service'<br>
when HHC_Occupation_Cd = '4'<br>
then '4.Clerical/White Collar'<br>
when HHC_Occupation_Cd = '5'<br>
then '5.Craftsman/Blue Collar'<br>
end 'Occupation',<br>
count(HHC_Occupation_Cd)'Total',<br>
sum(case HH_Level when 'CUST' then 1<br>
else 0 end) 'Customers',<br>
sum(case HH_Level when 'PRSP' then 1<br>
else 0 end) 'Prospects'<br>
from Household <br>
where ((Member_Status = 'ACTV' and HH_Level = 'Cust') or (HH_Level = 'PRSP'))and<br>
(HHC_Occupation_Cd = '1' or <br>
HHC_Occupation_Cd = '2' or<br>
HHC_Occupation_Cd = '3' or<br>
HHC_Occupation_Cd = '4' or<br>
HHC_Occupation_Cd = '5')<br>
group by case<br>
when HHC_Occupation_Cd = '1'<br>
then '1.Professional/Technical'<br>
when HHC_Occupation_Cd = '2'<br>
then '2.Administrative/Managerial'<br>
when HHC_Occupation_Cd = '3'<br>
then '3.Sales/Service'<br>
when HHC_Occupation_Cd = '4'<br>
then '4.Clerical/White Collar'<br>
when HHC_Occupation_Cd = '5'<br>
then '5.Craftsman/Blue Collar'<br>
end with rollup<br>
order by 1<br>
<br>
RESULTS<br>
Occupation Total Customers Prospects <br>
--------------------------- ----------- ----------- ----------- <br>
NULL 1132245 262385 869860<br>
1.Professional/Technical 476379 121861 354518<br>
2.Administrative/Managerial 249758 56758 193000<br>
3.Sales/Service 83283 17946 65337<br>
4.Clerical/White Collar 113428 22967 90461<br>
5.Craftsman/Blue Collar 209397 42853 166544<br>
<br>
(6 row(s) affected)<br>

 

[enotech]

Hi, I've had a similar problem where I've needed to make a crosstab query in SQL Server.

I ended up making a stored procedure that does it for me - just pass in a standard query and a few other parameters.

Here it us - I hope you find it useful:
--==============================================
create procedure sp_XTabQuery

@strSQL varchar(8000),
@ColumnHeading varchar(1000),
@Value varchar(1000),
@RowHeading varchar(1000),
@SortField varchar(1000),
@ValueOperator varchar(1000)

as

declare @FieldName varchar(1000), @FieldValue varchar(8000),@RowHeadingValue varchar(1000),@OrderByValue varchar(1000), @XTSQL varchar(8000)
set @XTSQL = 'select ' + @RowHeading + ', '
exec('declare mycursor cursor for select distinct ' + @ColumnHeading + ', ' + @SortField + ' from (' + @strSQL + ') x order by ' + @SortField)
open mycursor
fetch next from mycursor into @FieldName, @OrderByValue
While @@fetch_status = 0
begin
if @XTSQL <> 'select ' + @RowHeading + ', '
set @XTSQL = @XTSQL + ','
set @XTSQL = @XTSQL + '[' + @FieldName + '] = ' + @ValueOperator + '(case when ' + @ColumnHeading + ' = ''' + @FieldName + ''' then ' + @Value + ' else '''' end) '
fetch next from mycursor into @FieldName, @OrderByValue
end
set @XTSQL = @XTSQL + ' from (' + @strSQL + ') a'
set @XTSQL = @XTSQL + ' group by ' + @RowHeading
exec(@XTSQL)
close mycursor
deallocate mycursor

 

[rvr]

Thanks! I am definitely going to try both methods.

RvR