Convert Rows to Columns - For every FieldName that exists I need a column for it

Question

My question is very similar to Efficiently convert rows to columns in sql server. For every FieldName that exists, I need a column for it. The issue I am having is

I am creating many rows for each ID
I have an uncertain amount of columns. There are at least 2000 different FieldNames so I need something that is efficient
I need to have conditions based on if it's a string, numeric, or date field.

Original table:

CREATE TABLE [UWFieldTable] 
(
    [FieldName] nvarchar(25),
    [StringValue] nvarchar(25),
    [DateValue] date,
    [NumericValue] nvarchar(25),
    [Id] nvarchar(5)
)

INSERT INTO [UWFieldTable] VALUES ('UWName', 'Kim', NULL, NULL, 'A1')
INSERT INTO [UWFieldTable] VALUES ('UWDate', NULL, '1/9/2020', NULL, 'A1')
INSERT INTO [UWFieldTable] VALUES ('UWNumber', '3.3', NULL, '3.3', 'A2')
INSERT INTO [UWFieldTable] VALUES ('CloseName', 'Billy', NULL, NULL, 'A2')
INSERT INTO [UWFieldTable] VALUES ('CloseDate', NULL, '1/6/2020', NULL, 'A3')
INSERT INTO [UWFieldTable] VALUES ('CloseNumber', '30.6', NULL, '30.6', 'A3')
INSERT INTO [UWFieldTable] VALUES ('UWDate', NULL, '1/10/2020', NULL, 'A3')

FieldName   | StringValue | DateValue  | NumericValue | Id |
-------------------------------------------------------------
UWName      | Kim         | NULL       | NULL         | A1 |
UWDate      | NULL        | 2020-01-09 | NULL         | A1 |
UWNumber    | 3.3         | NULL       | 3.3          | A2 |
CloseName   | Billy       | NULL       | NULL         | A2 |
CloseDate   | NULL        | 2020-01-06 | NULL         | A3 |
CloseNumber | 30.6        | NULL       | 30.6         | A3 |
UWDate      | NULL        | 2020-01-10 | NULL         | A3 |
...

Desired output:

Id | UWName | UWDate     | UWNumber | CloseName | CloseDate  | CloseNumber |
--------------------------------------------------------------------------------
A1 | Kim    | 2020-01-09 | NULL     | NULL      | NULL       | NULL        |
A2 | NULL   | NULL       | 3.3      | Billy     | NULL       | NULL        |
A3 | NULL   | 2020-01-01 | NULL     | NULL      | 2020-01-10 | 30.6        |

Attempted code:

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME([FieldName]) 
                      FROM [UWFieldTable]
                      GROUP BY [FieldName]
                      ORDER BY [FieldName]
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = 'SELECT [Id], ' + @cols + ' from 
             (
                select *
                from [UWFieldTable]
            ) x
            PIVOT 
            (
                MAX([StringValue])
                FOR [FieldName] in (' + @cols + ')
            ) p order by [Id]'

EXECUTE(@query);

score 0 · Answer 1 · answered Jan 10 '20 at 02:04

Try this:

SELECT [Id], [UWName],[UWDate],[UWNumber],[CloseName],[CloseDate],[CloseNumber] from 
         (
            SELECT [Id],[FieldName], Val
            FROM (SELECT [FieldName], [StringValue], CAST([DateValue] AS nvarchar(25)) AS DateValue, [NumericValue], [Id] 
                  FROM [UWFieldTable]) AS srcUnpivot
            UNPIVOT ( Val FOR ColType IN ([StringValue], DateValue, [NumericValue])) AS unpvt
        ) x
        PIVOT
        (
           MAX([Val]) 
           FOR [FieldName] in ([UWName],[UWDate],[UWNumber],[CloseName],[CloseDate],[CloseNumber])
        )  AS pv1

ref: SQL Server Pivot on multiple fields

score 0 · Answer 2 · answered Jan 10 '20 at 02:43

for such case, it is easier to use conditional case statement with GROUP BY

SELECT  Id,
        UWName      = MAX(CASE WHEN [FieldName] = 'UWName'      THEN [StringValue] END),
        UWDate      = MAX(CASE WHEN [FieldName] = 'UWDate'      THEN [DateValue] END),
        UWNumber    = MAX(CASE WHEN [FieldName] = 'UWNumber'    THEN [NumericValue] END),
        CloseName   = MAX(CASE WHEN [FieldName] = 'CloseName'   THEN [StringValue] END),
        CloseDate   = MAX(CASE WHEN [FieldName] = 'CloseDate'   THEN [DateValue] END),
        CloseNumber = MAX(CASE WHEN [FieldName] = 'CloseNumber' THEN [NumericValue] END)
FROM    [UWFieldTable]
GROUP BY Id

Convert Rows to Columns - For every FieldName that exists I need a column for it

2 Answers2