sql-server - TSQL：基于 varchar 值的动态透视不能正常工作

Question

我正在使用 2 张桌子：

• zEaAttributes：用于字段列表
• zEAValues：我们想要转换的数据

我期望得到的结果将是这样的一行数据：

Name    Street Address  State   County  Zip
--------------------------------------------
Ajax    ABC 1st Ave.    TX      NULL    NULL

但相反，我得到了 3 行，如下所示：

Name    Street Address  State   County  Zip
---------------------------------------------
Ajax    NULL            NULL    NULL    NULL
NULL    ABC 1st Ave.    NULL    NULL    NULL
NULL    NULL            TX      NULL    NULL

下面是用于创建和填充我们正在测试的 2 个表的 SQL。请让我知道我做错了什么。

谢谢你。

CREATE TABLE [dbo].[zEaAttributes]
(
    [Ent_Id] [uniqueidentifier] NOT NULL,
    [Position] [smallint] NOT NULL,
    [Caption] [varchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[zEaAttributes] ([Ent_Id], [Position], [Caption]) 
VALUES ('3fc25249-6b2a-40e6-839d-7e91b01d989f', 1, 'Name')
INSERT INTO [dbo].[zEaAttributes] ([Ent_Id], [Position], [Caption]) 
VALUES ('3fc25249-6b2a-40e6-839d-7e91b01d989f', 2, 'Street Address')
INSERT INTO [dbo].[zEaAttributes] ([Ent_Id], [Position], [Caption]) 
VALUES ('3fc25249-6b2a-40e6-839d-7e91b01d989f', 3, 'State')
INSERT INTO [dbo].[zEaAttributes] ([Ent_Id], [Position], [Caption]) 
VALUES ('3fc25249-6b2a-40e6-839d-7e91b01d989f', 4, 'County')
INSERT INTO [dbo].[zEaAttributes] ([Ent_Id], [Position], [Caption]) 
VALUES ('3fc25249-6b2a-40e6-839d-7e91b01d989f', 5, 'Zip')

CREATE TABLE [dbo].[zEAValues]
(
    [Ent_Id] [uniqueidentifier] NOT NULL,
    [Position] [smallint] NOT NULL,
    [Caption] [varchar](50) NULL,
    [Value] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

INSERT INTO [dbo].[zEAValues] ([Ent_Id], [Position], [Caption], [Value]) 
VALUES ('3fc25249-6b2a-40e6-839d-7e91b01d989f', 1, 'Name', N'Ajax')
INSERT INTO [dbo].[zEAValues] ([Ent_Id], [Position], [Caption], [Value]) 
VALUES ('3fc25249-6b2a-40e6-839d-7e91b01d989f', 2, 'Street Address', N'ABC 1st Ave.')
INSERT INTO [dbo].[zEAValues] ([Ent_Id], [Position], [Caption], [Value]) 
VALUES ('3fc25249-6b2a-40e6-839d-7e91b01d989f', 3, 'State', N'TX')

--Get a list of the "Fields" (Columns) 
DECLARE @DynamicColumns AS VARCHAR(max)
 
SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ', '')
                         + Quotename(Caption)
FROM   (SELECT DISTINCT Caption, Position
        FROM   zEaAttributes
       ) AS FieldList
       ORDER BY Position
 
--Build the Dynamic Pivot Table Query  
DECLARE @FinalTableStruct AS NVARCHAR(max)
 
SET @FinalTableStruct = 'SELECT ' + @DynamicColumns +
' FROM zEAValues x PIVOT ( MAX( Value ) FOR Caption IN ('
                        + @DynamicColumns + ') ) p ' 
EXECUTE(@FinalTableStruct)

标签： sql-servertsqldynamic-pivot

Answer 1

位置列中的不同值导致PIVOT将值移动到相应的行。您需要在执行操作时删除该列，因为根据输出不需要它。以下将做到这一点：

SET @FinalTableStruct = 'SELECT ' + @DynamicColumns +
                        ' FROM (select caption, [Value] from zEAValues) x 
                        PIVOT 
                        ( 
                            MAX(Value) FOR Caption IN ('+ @DynamicColumns + ')
                        ) p' 
print @FinalTableStruct
EXECUTE(@FinalTableStruct)