How to convert row to cloumn with PIVOT ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hello guys , I have a store proceduce get column with parameter , but i try and see it same error .

SQL

CREATE PROC GET_COLUMN_VALUE @GID CHAR(3)
AS
BEGIN
	DECLARE @pivot_cols NVARCHAR(MAX)
	SELECT @pivot_cols= STUFF((SELECT DISTINCT ','+ QUOTENAME(B.FUNCTION_MOD_NAME)
		FROM FUNCTION_GROUP A INNER 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 @pivot_query NVARCHAR(MAX)
    SET @pivot_query = '
    ;WITH CTE AS (
    SELECT A.GROUP_MOD_ID, B.FUNCTION_MOD_NAME, CAST(ALLOW AS INT) AS ALLOW
    FROM FUNCTION_GROUP  A INNER JOIN FUNCTION_MOD B ON A.FUNCTION_MOD_ID = B.FUNCTION_MOD_ID )
		SELECT GROUP_MOD_ID, '+@pivot_cols+'
		FROM CTE 
    PIVOT (MAX(ALLOW) FOR FUNCTION_MOD_NAME IN ('+@pivot_cols+')) AS PIV'

PRINT @pivot_query
	EXEC(@pivot_query)  
END

EXEC GET_COLUMN_VALUE G02

After run i see result as

HTML

GROUP_MOD_ID	Add   Delete Edit
G01		1     NULL   1
G02		1     0      1

Okey, You can tell me about this problem and give me some advice how do it working.
Thank guys.

Posted 13-May-15 3:43am

headshot9x

Add a Solution

Comments

OriginalGriff 13-May-15 9:59am

What error?
What does it do that you didn't expect, or not do that you did?

headshot9x 13-May-15 10:07am

Hi OriginalGriff.
As you see it, I want get data where @gid=G02 , but result have G02 and G01 ??? .I think i should GROUP BY in store procedure ?

OriginalGriff 13-May-15 10:10am

No, I don't see it :laugh:
All I see is a lump of code (that you say has an error) and you say "After run i see result as" without saying what the inputs are or what you expect to get.
We can't read your mind here!

headshot9x 13-May-15 10:41am

Oh , I`m so sory .I `ll describe by follow
- First , I created a Store Procedure with name 'GET_COLUMN_VALUE' with parameter @GID , ok it is success.
- Second, i run Store Procedure with parameter G02 as 'EXEC GET_COLUMN_VALUE G02' and i get the result as you see (it have two row in here ). Note , i only get data with 'G02' .What happen in here ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-05-13T09:11:00

You need to pass the @GID parameter to the dynamic PIVOT query. Use sp_executesql[^] to pass the parameter to the dynamic query:

SQL

DECLARE @pivot_query NVARCHAR(MAX)
SET @pivot_query = N'WITH CTE AS
(
    SELECT A.GROUP_MOD_ID, B.FUNCTION_MOD_NAME, CAST(ALLOW AS INT) AS ALLOW
    FROM FUNCTION_GROUP  A INNER JOIN FUNCTION_MOD B ON A.FUNCTION_MOD_ID = B.FUNCTION_MOD_ID
    WHERE A.GROUP_MOD_ID = @GID
)
SELECT GROUP_MOD_ID, ' + @pivot_cols + N'
FROM CTE
PIVOT (MAX(ALLOW) FOR FUNCTION_MOD_NAME IN (' + @pivot_cols + N')) AS PIV';

PRINT @pivot_query

EXEC dbo.sp_executesql @pivot_query, N'@GID char(3)', @GID = @GID;