在SQL Server中为列名称创建动态选择获取值 [英] Create dynamic select get value for column name - in SQL Server

问题描述

请帮助我创建一个选择SQL语句，其结果列名称将从原始表中的列值(表名称为Device_Part)中获取:

Please help me create a select SQL statement with the results column name get from the column values in origin table (tablename is Device_Part):

用户可以输入许多具有许多动态PartTypeName的DeviceCode，PartTypeName值为PartInfo.

User can input many DeviceCode which have many dynamic PartTypeName, the PartTypeName value is the PartInfo.

推荐答案

这可能会有所帮助:

CREATE Table Device (
DeviceCode NVARCHAR(100) NOT NULL,
PartTypeName NVARCHAR(100) NOT NULL,
PartInfo NVARCHAR(100) NOT NULL
)

Insert Into Device
Values('VT.SX-01','CPU','Pentium G6650'),
('VT.SX-01','Motherboard','H81M -  S2PV'),
('VT.SX-01','RAM','DDR# 4GB - bus 1866 - Nano'),
('VT.SX-01','PartType Name 01','PartInfo 01')

--QUERY
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnNamesInPivot AS NVARCHAR(MAX);

--Get distinct values of PIVOT Column 
SELECT   @ColumnNamesInPivot = ISNULL(@ColumnNamesInPivot + ',', '')
        + QUOTENAME([PartTypeName])
FROM    ( SELECT    DISTINCT
                    [PartTypeName]
          FROM      Device
        ) AS P



SELECT  @DynamicPivotQuery = N'Select DeviceCode,'
        + @ColumnNamesInPivot + ' 
            FROM    ( SELECT * 
          FROM      Device
        ) AS SourceTable PIVOT( MAX(PartInfo) FOR [PartTypeName] IN ('
        + @ColumnNamesInPivot + ') ) AS PVTTable'

EXEC sp_executesql @DynamicPivotQuery;

结果将是:

这篇关于在SQL Server中为列名称创建动态选择获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！