Convert MS Access TRANSFORM/PIVOT query to SQL Server

Question

I need to transform an access query to sql server query but I am getting an error, can someone point me to the error cause?

Here is the MS Access query:

TRANSFORM Avg([X Avg Sub Group]) AS [AvgOfX Avg Sub Group] SELECT Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl, Avg([X Avg Sub Group]) AS [Total Of X Avg Sub Group] FROM [Capability Data with Shift] WHERE (((Process)="BBB WELDING")) GROUP BY Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl PIVOT SHIFT_VAL0;

Here is what I have done:

DECLARE @cols AS NVARCHAR(MAX); DECLARE @query AS NVARCHAR(MAX); SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(avg([X Avg Sub Group])) FROM [Capability Data with Shift] WHERE (((Process)='BBB WELDING')) GROUP BY Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl, SHIFT_VAL0 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,''); SET @query = ' SELECT Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl, [Total Of X Avg Sub Group], ' + @cols + ' FROM ( SELECT Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl, SHIFT_VAL0, Avg([X Avg Sub Group]) AS [Total Of X Avg Sub Group] FROM [Capability Data with Shift] WHERE (((Process)=''BBB WELDING'')) GROUP BY Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl, SHIFT_VAL0 ) t PIVOT ( avg([Total Of X Avg Sub Group]) FOR SHIFT_VAL0 IN( ' + @cols + ') ) p '; Execute(@query);

This is the result I am getting when I execute the query in SQL Server:

(1 row(s) affected)

Msg 8114, Level 16, State 1, Line 17 Error converting data type nvarchar to int.

Msg 473, Level 16, State 1, Line 17 The incorrect value "0.000642857" is supplied in the PIVOT operator.

Msg 207, Level 16, State 1, Line 1 Invalid column name 'Total Of X Avg Sub Group'.

Try printing your dynamic sql string before executing it. This should be always be done anyway so you actually know what you are running. — Sean Lange
– Sean Lange, Commented Dec 20, 2018 at 15:46

Barrett Wakefield · Accepted Answer · 2022-07-29 22:57:09Z

A multi-user version without cursor:

CREATE OR ALTER PROCEDURE [dbo].msrTransformAsAccessDo @TRANSFORM_Function nvarchar(MAX), @TRANSFORM_Field nvarchar(MAX), @SQL_SELECT nvarchar(MAX), @SQL_FROM_WHERE nvarchar(MAX), @SQL_GROUPBY_HAVING_ORDERBY nvarchar(MAX), @PIVOTBY nvarchar(MAX) AS BEGIN SET NOCOUNT ON DECLARE @Values nvarchar(MAX) = N'' DECLARE @tmpStr nvarchar(MAX) DECLARE @RowCnt INT DECLARE @id int = 1 DECLARE @DynSQL nvarchar(MAX) = N'INSERT INTO #pivoting SELECT DISTINCT ' + @PIVOTBY + ' as key_value ' + @SQL_FROM_WHERE + ' ORDER BY ' + @PIVOTBY EXEC (@DynSQL) SELECT @RowCnt = COUNT(*) FROM #pivoting; --replace cursor WHILE @id <= @RowCnt BEGIN SELECT @tmpStr = key_value from #pivoting where id = @id SET @Values = @Values + ', ' + @TRANSFORM_Function + '(Case When convert(nvarchar,' + @PIVOTBY + ')=convert(nvarchar,''' + @tmpStr + ''') Then ' + @TRANSFORM_Field + ' Else Null End) AS [' + @tmpStr + '] ' SET @id += 1 END SET @DynSQL = @SQL_SELECT + ' ' + @Values + ' ' + @SQL_FROM_WHERE + ' ' + @SQL_GROUPBY_HAVING_ORDERBY EXEC (@DynSQL) END

Also need to create temp table before you call the procedure (global temp is not session specific):

BEGIN --create temp table to ensure each user session has their own version of the pivot CREATE TABLE #pivoting (id INT IDENTITY NOT NULL, key_value NVARCHAR(max)) EXECUTE [dbo].msrTransformAsAccessDo 'Avg', '[X Avg Sub Group]', 'SELECT Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl, Avg([X Avg Sub Group]) AS [Total Of X Avg Sub Group] ', 'FROM [Capability Data with Shift] WHERE (((Process)=''BBB WELDING'')) ', 'GROUP BY Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl ', 'SHIFT_VAL0' DROP TABLE #pivoting END;

4dmonster · Accepted Answer · 2018-12-21 09:54:06Z

I have such universal stored procedure for pivoting

CREATE PROCEDURE [dbo].[msrTransformAsAccessDo] @TRANSFORM_Function nvarchar(max), @TRANSFORM_Field nvarchar(max), @SQL_SELECT nvarchar(max), @SQL_FROM_WHERE nvarchar(max), @SQL_GROUPBY_HAVING_ORDERBY nvarchar(max), @PIVOTBY nvarchar(max) AS BEGIN SET NOCOUNT ON DECLARE @DynSQL nvarchar(max) SET @DynSQL = N' SELECT DISTINCT ' + @PIVOTBY + ' as key_value into ##pivoting ' + @SQL_FROM_WHERE + ' ORDER BY ' + @PIVOTBY DROP TABLE IF EXISTS ##pivoting EXEC (@DynSQL) DECLARE @Values nvarchar(max) = N'' DECLARE @tmpStr nvarchar(max) DECLARE @rsk CURSOR SET @rsk = CURSOR SCROLL FOR select key_value from ##pivoting OPEN @rsk FETCH NEXT FROM @rsk INTO @tmpStr WHILE @@FETCH_STATUS = 0 BEGIN SET @Values = @Values + ', ' + @TRANSFORM_Function + '(Case When convert(nvarchar,' + @PIVOTBY + ')=convert(nvarchar,''' + @tmpStr + ''') Then ' + @TRANSFORM_Field + ' Else Null End) AS [' + @tmpStr + '] ' FETCH NEXT FROM @rsk INTO @tmpStr END CLOSE @rsk DROP TABLE IF EXISTS ##pivoting SET @DynSQL = @SQL_SELECT + ' ' + @Values + ' ' + @SQL_FROM_WHERE + ' ' + @SQL_GROUPBY_HAVING_ORDERBY EXEC (@DynSQL) END GO

And call for your case:

EXECUTE msrTransformAsAccessDo 'Avg', '[X Avg Sub Group]', 'SELECT Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl, Avg([X Avg Sub Group]) AS [Total Of X Avg Sub Group] ', 'FROM [Capability Data with Shift] WHERE (((Process)=''BBB WELDING'')) ', 'GROUP BY Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl ', 'SHIFT_VAL0'

Thank you this worked for me... changed the DROP TABLE IF EXISTS ##pivoting because I am using al older SQL Server version...

Collectives™ on Stack Overflow

Convert MS Access TRANSFORM/PIVOT query to SQL Server

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related