我在查询中旋转统计列没有问题,例如列1,列2,列3 ...等.
但是我想做的是动态的.
我的数据如下:
我希望能够执行存储过程以获取输出结果: Exec sp_output 1 (从另一个窗口,其中"1"代表PoolID(@AppPool))看起来像这样:
这是我的SP:
create PROCEDURE [dbo].[sp_test] @Query as nvarchar(100) -- OUTPUT?, @AppPool AS nvarchar(50) AS SELECT @Query = Attribute FROM [dbo].[Vy_UserAccess] WHERE PoolID = @AppPool SELECT [Users],'+ @Query +' FROM (SELECT [Pool],[Users],[RecNum],[Attribute],[Values] FROM [dbo].[Vy_UserAccess] ) AS T1 PIVOT (MAX([Values]) FOR [ATTRIBUTE] IN ('+ @Query +')) AS T2是否可以通过微调代码来实现?还是我必须朝另一个方向发展?
解决方案您可以在以下位置进行操作:
查询
CREATE PROCEDURE [dbo].[sp_test] @AppPool AS NVARCHAR(60) AS DECLARE @cols AS NVARCHAR(MAX) = '', @sql AS NVARCHAR(MAX) SELECT @cols += QUOTENAME([Name]) + ',' FROM (SELECT DISTINCT Attribute as Name FROM [dbo].[Vy_UserAccess] WHERE PoolID = @AppPool ) a ORDER BY Name DESC SET @cols = LEFT(@cols, LEN(@cols) - 1) SET @sql = 'SELECT Users, ' + @cols + ' FROM ( SELECT [Pool],[Users],[RecNum],[Attribute],[Values] FROM [dbo].[Vy_UserAccess] ) AS T1 PIVOT (MAX([Values]) FOR [ATTRIBUTE] IN ('+ @cols +')) AS T2' EXEC sp_executesql @sql, N'@AppPool NVARCHAR(60)', @AppPool执行
Exec sp_test 1
查询示例数据
CREATE PROCEDURE sp_test @AppPool AS NVARCHAR(60) AS CREATE TABLE #test ( PoolId NVARCHAR(60), Pool NVARCHAR(40), Users NVARCHAR(60), RecNum INT, Attribute NVARCHAR(40), [Values] NVARCHAR(20) ) INSERT INTO #test VALUES ('1', 'FINANCE', 'User1', 2, 'DIVISION', '010'), ('1', 'FINANCE', 'User1', 1, 'COMPANY', '1'), ('1', 'FINANCE', 'User1', 1, 'DIVISION', '050') DECLARE @cols AS NVARCHAR(MAX) = '', @sql AS NVARCHAR(MAX) SELECT @cols += QUOTENAME([Name]) + ',' FROM (SELECT DISTINCT Attribute as Name FROM #test WHERE PoolID = @AppPool ) a ORDER BY Name DESC SET @cols = LEFT(@cols, LEN(@cols) - 1) SET @sql = 'SELECT Users, ' + @cols + ' FROM ( SELECT [Pool],[Users],[RecNum],[Attribute],[Values] FROM #test ) AS T1 PIVOT (MAX([Values]) FOR [ATTRIBUTE] IN ('+ @cols +')) AS T2' EXEC Sp_executesql @sql, N'@AppPool NVARCHAR(60)', @AppPool DROP TABLE #test输出
Users DIVISION COMPANY User1 050 1 User1 010 NULLThere is no problem for me to pivot statistic columns in my query e.g Column 1 , Column 2 , Column 3.... and so on.
But i would like to do this dynamic instead.
My data looks like this:
i want to be able to EXECUTE a store procedure to get the output result: Exec sp_output 1 (from another window where '1' represents the PoolID (@AppPool)) to look like this:
This is my SP:
create PROCEDURE [dbo].[sp_test] @Query as nvarchar(100) -- OUTPUT?, @AppPool AS nvarchar(50) AS SELECT @Query = Attribute FROM [dbo].[Vy_UserAccess] WHERE PoolID = @AppPool SELECT [Users],'+ @Query +' FROM (SELECT [Pool],[Users],[RecNum],[Attribute],[Values] FROM [dbo].[Vy_UserAccess] ) AS T1 PIVOT (MAX([Values]) FOR [ATTRIBUTE] IN ('+ @Query +')) AS T2Is this possible to achieve by just fine tuning my code or do i have to go on another direction?
解决方案You can do It in following:
QUERY
CREATE PROCEDURE [dbo].[sp_test] @AppPool AS NVARCHAR(60) AS DECLARE @cols AS NVARCHAR(MAX) = '', @sql AS NVARCHAR(MAX) SELECT @cols += QUOTENAME([Name]) + ',' FROM (SELECT DISTINCT Attribute as Name FROM [dbo].[Vy_UserAccess] WHERE PoolID = @AppPool ) a ORDER BY Name DESC SET @cols = LEFT(@cols, LEN(@cols) - 1) SET @sql = 'SELECT Users, ' + @cols + ' FROM ( SELECT [Pool],[Users],[RecNum],[Attribute],[Values] FROM [dbo].[Vy_UserAccess] ) AS T1 PIVOT (MAX([Values]) FOR [ATTRIBUTE] IN ('+ @cols +')) AS T2' EXEC sp_executesql @sql, N'@AppPool NVARCHAR(60)', @AppPoolEXECUTION
Exec sp_test 1
QUERY WITH SAMPLE DATA
CREATE PROCEDURE sp_test @AppPool AS NVARCHAR(60) AS CREATE TABLE #test ( PoolId NVARCHAR(60), Pool NVARCHAR(40), Users NVARCHAR(60), RecNum INT, Attribute NVARCHAR(40), [Values] NVARCHAR(20) ) INSERT INTO #test VALUES ('1', 'FINANCE', 'User1', 2, 'DIVISION', '010'), ('1', 'FINANCE', 'User1', 1, 'COMPANY', '1'), ('1', 'FINANCE', 'User1', 1, 'DIVISION', '050') DECLARE @cols AS NVARCHAR(MAX) = '', @sql AS NVARCHAR(MAX) SELECT @cols += QUOTENAME([Name]) + ',' FROM (SELECT DISTINCT Attribute as Name FROM #test WHERE PoolID = @AppPool ) a ORDER BY Name DESC SET @cols = LEFT(@cols, LEN(@cols) - 1) SET @sql = 'SELECT Users, ' + @cols + ' FROM ( SELECT [Pool],[Users],[RecNum],[Attribute],[Values] FROM #test ) AS T1 PIVOT (MAX([Values]) FOR [ATTRIBUTE] IN ('+ @cols +')) AS T2' EXEC Sp_executesql @sql, N'@AppPool NVARCHAR(60)', @AppPool DROP TABLE #testOUTPUT
Users DIVISION COMPANY User1 050 1 User1 010 NULL
更多推荐
将动态行转换为具有输出参数的列?
发布评论