Pivot Conversion HELP

Question

Pivot Conversion HELP

Shailen-214846

SSC Enthusiast

Points: 118
More actions
March 9, 2005 at 6:20 am

#66060

Hi Guys
I need help desperately. My problem is also the Pivot functionality in sql server 2000
I am trying to convert my Access sql to server. My access code is
TRANSFORM Sum([3rollupall].SumOfSumOfBOND_AMOUNT) AS SumOfSumOfSumOfBOND_AMOUNT
SELECT [3rollupall].SUBURB, [3rollupall].price_band, Sum([3rollupall].SumOfSumOfBOND_AMOUNT) AS [Total Of SumOfSumOfBOND_AMOUNT]into [3rollupall_Crosstab]
FROM [3rollupall]
GROUP BY [3rollupall].SUBURB, [3rollupall].price_band
PIVOT [3rollupall].institution;
Any ideas how i can convert this??
Thanks a million

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Unfortunately, there is no PIVOT in SQL Server 2000 - it will be in SS 2005, though..

I don't speak Access too well, but perhaps if you provided some sample data and the desired output, I could figure out how to do it.

It's doable to pivot in SS2k (within certain limits), but it usually requires a few more lines of code than when there's a native pivot function available.

/Kenneth

Shailen-214846 SSC Enthusiast Points: 118 More actions · Answer 2

Hi

Thanks for the response

My input table contains the following

SUBURB price_band institution SumOfSumOfBOND_AMOUNT

EASTERN CAPEB. R100k - R250k ABSA 38941775

EASTERN CAPEB. R100k - R250kFNB21252950

EASTERN CAPEB. R100k - R250kINVESTEC200000

EASTERN CAPEB. R100k - R250kNEDCOR21482912

and the output using the pivot query results in

SUBURBPrice_BandTotal Of SumOfSumOfBOND_AMOUNTABSAFNBINVESTECNEDCOROTHEROTHER BANKSTANDARD

EASTERN CAPEB. R100k - R250k1235680283894177521252950200000214829125970108996797925752304

EASTERN CAPEC. R250k - R500k12650250439100400238875051830000224571295518350627522027433900

EASTERN CAPED. R500k - R750k573089001898490079990001275000103450003070000358000012055000

rhunt SSCertifiable Points: 5868 More actions · Answer 3

Shailen, do an index search on "pivot tables" in SQL Server's Books On-Line (BOL). You will find a very clear illustration on how to pivot using a SQL's "CASE" statement. Take a look at that and respond back if that does not do what you need.

Shailen-214846 SSC Enthusiast Points: 118 More actions · Answer 4

Thanks for the response. I have seen all the code used but some how i cannot get it to execute the command i want? Any idea's on how i should write the code. I have been sitting with this issue for the last week now

Shailen-214846 SSC Enthusiast Points: 118 More actions · Answer 5

I am trying to incorporate my query but using this function

CREATE PROCEDURE crosstab

@select varchar(8000),

@pivot varchar(100),

@sumfunc varchar(100),

@table varchar(100),

@where varchar(1000)='1=1'

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 ' + @where + ' 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

Any ideas how i can structure my query for this function