question

technette avatar image
technette asked

Cross Tab Query for Report

Hi! How do I create one query that will allow me to have Row and details according to the following queries? The PART_ID should be the key. *****ROWS******** SELECT PHYS_COUNT_ID, TAG_NO, GROUP_NO, PART_ID, LOCATION_ID, COUNT_QTY, COUNT_USER_ID, RECOUNT_QTY, COUNT_DATE, TRACE_ID FROM PhyTag AS PT WHERE (COUNT_DATE ] cross-tab
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
technette avatar image
technette answered
I found a what seems to be a really nice stored procedure. However, I'm getting an error when I try to use it. Maybe someone can help me understand all the parameters I have to supply. Please see the my query and the error following this stored procedure. CREATE PROCEDURE [dbo].[crosstab] @select varchar(8000), @sumfunc varchar(100), @pivot 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 Here's my query: EXECUTE crosstab 'SELECT PHYS_COUNT_TAG.PHYS_COUNT_ID, PHYS_COUNT_TAG.TAG_NO, PHYS_COUNT_TAG.TRACE_ID, PHYS_COUNT_TAG.PART_ID, PHYS_COUNT_TAG.COUNT_QTY INVENTORY_TRANS.[TYPE], INVENTORY_TRANS.CLASS, INVENTORY_TRANS.QTY INVENTORY_TRANS.ACT_MATERIAL_COST from PHYS_COUNT_TAG inner join INVENTORY_TRANS on (PHYS_COUNT.PART_ID = INVENTORY_TRANS.PART_ID) group by PHYS_COUNT_TAG.PHYS_COUNT_ID', 'INVENTORY_TRANS.[TYPE], INVENTORY_TRANS.CLASS, sum(INVENTORY_TRANS.QTY)' This is my error: Msg 201, Level 16, State 4, Procedure crosstab, Line 0 Procedure or function 'crosstab' expects parameter '@pivot', which was not supplied.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

In order to understand how this will work, I would declare the variables to be their name (e.g. @Pivot = '@PIVOT') and change (a copy of) the SP so that the EXEC statements become PRINT ones. This won't allow it to complete, but it will allow you to see what it's doing with the parameters, and therefore the SQL statement it's trying to assemble. This should help you identify what to put in each parameter.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.