如何用PIVOT将行转换为cloumn? [英] How to convert row to cloumn with PIVOT ?
本文介绍了如何用PIVOT将行转换为cloumn?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,我有一个商店程序获取带参数的列,但我尝试看到同样的错误。
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 ,< span class =code-digit> 1 ,' ')
DECLARE @ pivot_query NVARCHAR (MAX)
SET @ pivot_query = '
; WITH CTE AS(
SELECT A.GROUP_MOD_ID,B.FUNCTION_MOD_NAME,CAST(ALLOW AS IN) T)AS允许
来自FUNCTION_GROUP内部联接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 < span class =code-sdkkeyword> @ pivot_query
EXEC ( @ pivot_query )
END
EXEC GET_COLUMN_VALUE G02
运行后我看到结果为
GROUP_MOD_ID添加删除编辑
G01 1 NULL 1
G02 1 0 1
Okey,你可以告诉我这个问题并给我一些建议如何运作。
谢谢你们。
解决方案
您需要将@GID
参数传递给动态PIVOT
查询。使用 sp_executesql [ ^ ]将参数传递给动态查询:
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)允许
来自FUNCTION_GROUP内部联接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 FUNCT ION_MOD_NAME IN(' + @ pivot_cols + N ' ))AS PIV';
PRINT @ pivot_query
EXEC dbo.sp_executesql @ pivot_query ,N ' @ GID char(3)', @ GID = @ GID < /跨度>;
Hello guys , I have a store proceduce get column with parameter , but i try and see it same error .
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
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.
解决方案
You need to pass the@GID
parameter to the dynamicPIVOT
query. Use sp_executesql[^] to pass the parameter to the dynamic query:
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;
这篇关于如何用PIVOT将行转换为cloumn?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文