x

Convert Access TRANSFORM to a SQL Crosstab

I've got a Crosstab Query in Access that I'm trying to convert to SQL 2005 and after beating my head for 3 days, I'm wondering if someone can help me out a bit.

TRANSFORM Max(tblPivotTest.MISPICKS) AS MaxOfMISPICKS
SELECT tblPivotTest.PN, tblPivotTest.FCODE, tblPivotTest.Slot
FROM tblPivotTest
GROUP BY tblPivotTest.PN, tblPivotTest.FCODE, tblPivotTest.Slot
PIVOT tblPivotTest.PERIOD;

Any help would be appreciated.

Thanks, Marty
more ▼

asked Jul 24 '12 at 09:27 PM in Default

martywestra gravatar image

martywestra
0 1 1 1

Man, this WYSIWYG box sure mangled my code. Let's try this.

TRANSFORM Max(tblPivotTest.MISPICKS) AS MaxOfMISPICKS

SELECT tblPivotTest.PartNum, tblPivotTest.FCODE, tblPivotTest.Slot FROM tblPivotTest

GROUP BY tblPivotTest.PartNum, tblPivotTest.FCODE, tblPivotTest.Slot

PIVOT

tblPivotTest.PERIOD;
Jul 24 '12 at 09:33 PM martywestra
indent each line by four spaces, or use the "CODE" button on the main text editor... I've done this in the main question...
Jul 25 '12 at 07:48 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

How many distinct values exist for tblPivotTest.PERIOD? If there are only few static values which can be hardcoded in the code, then PIVOT examples here should help. But if there are many values then dynamic sql can be used

DECLARE @Periods VARCHAR(MAX)
SELECT  @Periods = STUFF(( SELECT DISTINCT
                                    ',[' + CAST(PERIOD AS VARCHAR(50)) + ']'
                           FROM     [tblPivotTest]
                         FOR XML PATH('')), 1, 1, '') 

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = '
select
    *
from (
     SELECT PartNum, FCODE, Slot,MISPICKS,PERIOD 
     FROM tblPivotTest
  ) Data
PIVOT (
  Max(MISPICKS)
  FOR [PERIOD]
 IN (' + @Periods + ' )
) PivotTable
'

PRINT @SQL

--EXEC(@SQL)
more ▼

answered Jul 25 '12 at 09:34 AM

robbin gravatar image

robbin
1.6k 1 3 5

I've only got 3 distinct values, but that extra code for dynamic values is sweet.

It works on my test system, but not my production system. That database is running in SQL Server 2000 Compatibility Mode.

I will contact the software manufacturer to see if it's ok to raise it, but I'm wondering, if they say it's ok, can I raise the compatibility level in the middle of the day, or do I have to wait until this weekend to make sure everyone is out?

Thanks, Marty

Jul 25 '12 at 02:03 PM martywestra

Raising the compatibility level is an option but proper testing is always the pre-requisite. Also, this can be done without meddling with the compatibility level. See these articles by Jeff Moden.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Jul 27 '12 at 07:10 AM robbin
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x76
x4

asked: Jul 24 '12 at 09:27 PM

Seen: 1028 times

Last Updated: Jul 27 '12 at 07:37 AM