CODEWITHSUNDEEP
sql-server
Headshot
Headshot

Reputation: 452

How to convert row to column in SQL?

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:

enter image description here

After I try using pivot

enter image description here

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.

Upvotes: 0

Views: 125

Answers (1)

John Bell
John Bell

Reputation: 2350

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'

Upvotes: 1

Related Questions