Cross tab proc question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cross tab proc question

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-02-17 : 11:37:59

I am trying to test the Rob's Dynamic Cross Tab procedure [url]http://www.sqlteam.com/item.asp?ItemID=2955[/url]
After I create the PROC in my DB, I ran the following EXEC statement.

Exec crosstab 'select location.name  from location,location_xref,claim
 where location.location_key = location_xref.location_key and
       claim.ref_location= location_xref.location_key and
       ref_loc1 = 0000  group by location.name','count(claim_key)', 'year(Injury_year)', 'claim'

Howver, the Injury_year cloum turn out to be wrong.

name	1981	1887	2000	1994	2001	1985	1964	2004	1997	1988	1999	1903	2003	1983	1990	1982	1973	1984	1929	1992	2002	1995	1986	1993	1987	1978	1949	1989	1991	1963	1919	1904	1961	1939	1996	1998
AA	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
BB	0	0	0	0	0	0	0	0	1	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
CC	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
DD	0	0	1	0	0	0	0	0	1	0	1	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
EE	0	0	1	2	3	0	0	1	4	0	3	0	1	0	0	0	0	0	0	2	1	3	0	4	0	0	0	0	0	0	0	0	0	0	2	3
FF	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

The output should looks like:

name	1991	1992	1993	1994	1995	1996	1997	1998	1999	2000	2001	2002	2003	2004
AA	0	0	0	0	1	0	0	0	0	0	0	0	0	0
BB	0	0	0	0	0	0	0	0	1	0	0	0	2	0
CC	0	0	0	0	0	0	0	0	1	0	0	0	0	0
DD	0	0	1	0	0	0	0	0	1	0	1	0	0	0

Does anyone can tell me WHY the Year(Injury_year) function is not working correctly? / What I am doing wrong?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-17 : 22:15:03

There's a tweaked version in the comments section:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216

That lets you order the column headings the way you want. You can also specify a WHERE clause for the pivoted columns to restrict which headings you want.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-02-18 : 10:58:12

Rob,

I ran a tweaked version, but it keep giving me a syntax error:
Server: Msg 170, Level 15, State 1, Procedure crosstabwhere, Line 14
Line 14: Incorrect syntax near 'IsNull'.
I am looking at your code for 2 hours and still can't figure it out..

CREATE PROCEDURE crosstabwhere 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100),
@where varchar(1000)=null
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 ' + IsNull(@where,'1=1') + ' AND ' + @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

Thank you for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources