i want to migrate crosstab query in access to sql server 2005

Question

i want to migrate crosstab query in access to sql server 2005

YOGRAJ_T2002

SSC Journeyman

Points: 96
More actions
January 25, 2008 at 5:09 am

#202716

hi,
i want to migrate the access crosstab query to sql server 2005
here is the query
TRANSFORM First([BQ_PRODBASE].[SANDCODE]) AS [The Value]
SELECT [BQ_PRODBASE].[PRODGRADESBCODE], [BQ_PRODBASE].[PRODGRADE], [BQ_PRODBASE].[SPGRAVITY], [BQ_PRODBASE].[PRODCODESAP], [BQ_PRODBASE].[SAPDESC], [BQ_PRODBASE].[PRODCODEIP], [BQ_PRODBASE].[PRODCODERPMS], [BQ_PRODBASE].[PRODCODEI2], [BQ_PRODBASE].[RLYCLASS], [BQ_PRODBASE].[LEGACYCODE], [BQ_PRODBASE].[REMARK], Format([BQ_PRODBASE].[GROUP],"True/False") AS [Group], [BQ_PRODBASE].[CONVERSION]
FROM BQ_PRODBASE
GROUP BY [BQ_PRODBASE].[PRODGRADESBCODE], [BQ_PRODBASE].[PRODGRADE], [BQ_PRODBASE].[GROUP], [BQ_PRODBASE].[SPGRAVITY], [BQ_PRODBASE].[PRODCODESAP], [BQ_PRODBASE].[SAPDESC], [BQ_PRODBASE].[PRODCODEIP], [BQ_PRODBASE].[PRODCODERPMS], [BQ_PRODBASE].[PRODCODEI2], [BQ_PRODBASE].[RLYCLASS], [BQ_PRODBASE].[LEGACYCODE], [BQ_PRODBASE].[REMARK], [BQ_PRODBASE].[CONVERSION]
PIVOT [BQ_PRODBASE].[MODELINITIALS];
can anyone help me
yogesh

Viewing 4 posts - 1 through 4 (of 4 total)

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

Before SQL Server 2005 you had to use a number of CASE statements to get anywhere near a Pivot table style result :crazy:. In 2005 they introduced the operator clause, PIVOT. This is explained in depth in BoL but I found it quite difficult to get my head around it at first and after a lot of trial and error found that PIVOT was useful, but only when you knew what columns and answers were required beforehand; no good for dynamic reporting.

I have written the following code to exec a stored procedure on a table to dynamically produce a pivotted report. I have filled in the variables for you from your Access query (I guessed that you have the same table and column names in your SS2005 setup?!?)

DECLARE@tableName VARCHAR(50)

,@tableColumns VARCHAR(4000)

,@summaryName VARCHAR(10)

,@summaryColumn VARCHAR(255)

,@pivotColumnName VARCHAR(255)

,@pivotColumnResults VARCHAR(4000)

-- Set Variables for Procedure

SELECT @tableName = 'BQ_PRODBASE',

@tableColumns = '[PRODGRADESBCODE], [PRODGRADE], [SPGRAVITY], ' +

'[PRODCODESAP], [SAPDESC], [PRODCODEIP], ' +

'[PRODCODERPMS], [PRODCODEI2], [RLYCLASS], ' +

'[LEGACYCODE], [REMARK], [GROUP], [CONVERSION], ' +

'[MODELINITIALS], [SANDCODE]',

@summaryName = 'COUNT',

@summaryColumn = '[SANDCODE]',

@pivotColumnName = '[MODELINITIALS]'

-- Set Headings for Pivoted Rows/Columns

SELECT @pivotColumnResults = ''

SELECT @pivotColumnResults = @pivotColumnResults + '[' + distinctTable.distinctCol + '], '

FROM (SELECT DISTINCT [MODELINITIALS] AS distinctCol FROM BQ_PRODBASE)distinctTable

SELECT @pivotColumnResults = LEFT(@pivotColumnResults, LEN(@pivotColumnResults) - 1)

-- Execute Pivot

EXEC spPivotTable @tableName, @tableColumns, @summaryName, @summaryColumn, @pivotColumnName, @pivotColumnResults

The variable settings are as follows:

@tablename is your source table and should match the table name you type (in red) in the 'Set Headings for Pivoted Rows/Columns' section.

@tableColumns is the entire list of columns you wish to show in your result set. This includes the column to be pivotted on and the column to be summarised.

@summaryName is the type of summary you wish to perform; AVG, SUM, MAX etc

@summaryColumn is the name of the column that you wish to summarise.

@pivotColumnName is the column name that you wish to pivot on and should match the column name you type (in red) in the 'Set Headings for Pivoted Rows/Columns' section.

@pivotColumnResults is the distinct list of values from the column that you wish to pivot on. These are calculated for you by the 'Set Headings for Pivoted Rows/Columns' section in which you need to provide the table and column names (in red).

I've attached the code for spPivotTable. Between this and the Access code you should be able to work out how it works and how to change the above code for other tables and pivots.

HTH,

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

YOGRAJ.T2002 Newbie Points: 3 More actions · Answer 2

THANK'S

ACTUALLY I M QUITE NEW TO THIS FIELD,

SO I DON'T KNOW THE PROCEDURE OF DYNAMIC REPORTING,

COULD YOU PLEASE HELP ME,

YOGESH

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 3

Here are two short articles that might help.

This link describes a very simple Access crosstab and how to re-create in either SQL 2000 or 2005:

http://wvmitchell.blogspot.com/2007_03_01_archive.html

This link describes a simple 2005 version using common table expressions (CTE) and the PIVOT statement:

http://www.wvmitchell.com/tips/Pivot%20example%20-%20SQL%20Server%202005.pdf