TSQL: Dynamic pivot based on varchar values not working correctly

Question

I have 2 tables I’m working with:

zEaAttributes: Used for the field list
zEAValues: The data we want to pivot

The results I expect to get will be one row of data like this:

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

But instead, I'm getting 3 rows that look like this:

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

And below is the SQL to create and populate the 2 tables we’re testing with. Please let me know what I’m doing wrong.

Thank you.

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)

user_12486643 · Answer

Your 3 rows output will be fixed by adding GROUP BY [Ent_Id] to the related query. Use a query like this for PIVOT:

SET @FinalTableStruct = 
  'SELECT * FROM 
  (
    SELECT [Ent_Id], [Caption], [Value] FROM zEAValues
  ) x 
  PIVOT 
  ( 
    MAX([Value]) FOR Caption IN (' + @DynamicColumns + ') 
  ) p'

By using this query the database engine will utilize GROUP BY [Ent_Id] automatically.

user_3987784 · Answer

Different values in the position column is causing PIVOT to move the values to the corresponding row. You need to drop the column while doing the operation as it's not needed as per the output. Following would do that:

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

TSQL: Dynamic pivot based on varchar values not working correctly

2 Answers