I have a stored procedure (join two tables and select where condition @GID), a want to convert table result from rows to columns. I use a dynamic pivot query.
My stored procedure:
After I try using pivot
I want result like this:
GROUP_MOD_ID ADD EDIT DELETE ETC...
---------------------------------------
G02 1 1 0 ....
Can you give me some advice about this ?
Thank you.
It's because you're using the batch delimiter to separate your queries. This means the scope of @GID is incorrect. Remove the semi colon after:
DECLARE @pivot_cols NVARCHAR(MAX);
You don't need to use batch delimiters in this case. The logical flow of the procedure means you can omit them without any problems.
EDIT:
Here's the edited code that I've devised:
ALTER PROCEDURE GET_COLUMN_VALUE @GID CHAR(3)
AS
BEGIN
DECLARE @PivotCols NVARCHAR(MAX)
SELECT @PivotCols = STUFF((SELECT DISTINCT ' , ' + QUOTENAME(B.FUNCTION_MOD_NAME)
FROM FUNCTION_GROUP AS A
JOIN FUNCTION_MOD B
ON A.FUNCTION_MOD_ID = B.FUNCTION_MOD_ID
WHERE A.GROUP_MOD_ID = @GID
FOR XML PATH (' '), TYPE).value(' . ', 'NVARCHAR(MAX) '), 1, 1, ' ')
DECLARE @PivotQuery NVARCHAR(MAX)
SET @PivotQuery = '
;WITH CTE AS (
SELECT A.GROUP_MOD_ID, B.FUNCTION_MOD_NAME, CAST(ALLOW AS BIT) AS ALLOW
FROM FUNCTION_GROUP AS A
JOIN FUNCTION_MOD AS B
ON A.FUNCTION_MOD_ID = B.FUNCTION_MOD_ID)
SELECT GROUP_MOD_ID, '+@PivotCols+'
FROM CTE
PIVOT (MAX(ALLOW) FOR FUNCTION_MOD_NAME IN ('+@PivotCols')) AS PIV'
PRINT @PivotQuery
EXEC (@PivotQuery)
END
EDIT2:
You should execute this stored procedure like so:
EXEC GET_COLUMN_VALUE @GID='G02'
GET_COLUMN_VALUE G02 It error Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'A'. Please see it
PIVOT query, you've replicated the JOIN line twice, which will create a syntax error. Put this line before your Exec (@pivot_query) so you can see what it's trying to execute: PRINT @pivot_query Also, you should be calling your procedure like so: EXEC GET_COLUMN_VALUE @GID='G02'GET_COLUMN_VALUE 'G02'GET_COLUMN_VALUE G02 and after put PRINT @pivot_query , i get error follow SELECT A.GROUP_MOD_ID, [Add],[Delete],[Edit] FROM (SELECT A.GROUP_MOD_ID,B.FUNCTION_MOD_NAME,CAST(ALLOW AS BIT) ALLOW FROM FUNCTION_GROUP A INNER JOIN FUNCTION_MOD B ON A.FUNCTION_MOD_ID=B.FUNCTION_MOD_ID) FUNCTION_GROUP A INNER JOIN FUNCTION_MOD B ON A.FUNCTION_MOD_ID=B.FUNCTION_MOD_ID PIVOT (MAX(ALLOW) FOR B.FUNCTION_MOD_NAME IN([Add],[Delete],[Edit])) AS P; Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'A'.