将动态行转换为具有输出参数的列? [英] Pivot Dynamic row to Column with output parameter?

问题描述

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

OUTPUT

Users   DIVISION    COMPANY
User1   050         1
User1   010         NULL

这篇关于将动态行转换为具有输出参数的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！