将动态行转换为具有输出参数的列?

本文介绍了将动态行转换为具有输出参数的列?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我在查询中旋转统计列没有问题，例如列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 NULL

There 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 T2

Is 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)', @AppPool

EXECUTION

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 #test