How to SQL PIVOT on two columns and with dynamic column names?

user_13274407 0
Viewed 255

I have a key value pair set of rows to associate to a unique identifier (ApplicationId).

The data would look something like this:

| ApplicationId | Key | Value | Date                  |
| 123           | A   | abc   | 2020-3-1 14:00:01.000 |
| 123           | B   | abd   | 2020-3-1 14:00:02.000 |
| 123           | C   | abe   | 2020-3-1 14:00:03.000 |
| 124           | A   | abf   | 2020-3-1 14:01:00.000 |
| 124           | D   | abg   | 2020-3-1 14:01:01.000 |

The end result i'm looking for would be this:

| ApplicationId | A   | A_Date                | B    | B_Date                | C    | C_Date                | D    | D_Date                |
| 123           | abc | 2020-3-1 14:00:01.000 | abd  | 2020-3-1 14:00:02.000 | abe  | 2020-3-1 14:00:03.000 | NULL | NULL                  |
| 124           | abf | 2020-3-1 14:01:00.000 | NULL | NULL                  | NULL | NULL                  | abg  | 2020-3-1 14:01:01.000 |

The Keys A,B,C,D are unknown so hard coding the column names isn't possible.

Here is something that works with one PIVOT

IF OBJECT_ID('tempdb.dbo.#_BLAH') IS NOT NULL DROP TABLE #_BLAH

SELECT et.[ApplicationId] et.[Key], et.[Value], et.[Date]
INTO #_BLAH
FROM ExampleTbl et
WHERE et.[Date] > DATEADD(dd, -1, GetDate())

DECLARE @_cols AS NVARCHAR(MAX)
DECLARE @_sql  AS NVARCHAR(MAX)

SELECT 
    @_cols += QUOTENAME([Key]) + ','
FROM 
    #_BLAH
GROUP BY
    [Key];

SET @_cols = STUFF((SELECT ',' + QUOTENAME(T.[Key])
                    FROM #_BLAH AS T
                    GROUP BY T.[Key]
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')


set @_sql = 'SELECT [ApplicationId], ' + @_cols + '
    FROM ( SELECT * FROM #_BLAH) AS SRC
    PIVOT ( MAX([Value]) FOR [Key] IN (' + @_cols + ') ) AS p';

EXEC(@_sql)

I've so far been unable to find an example or an article attempting to make a second dynamic column and adding in the value that relates the specific Key in my example.

My SQL above will accomplish creating the row i want except for the #_Date column i need.

pivot tsql sql-server
2 Answers

Try this:

DROP TABLE IF EXISTS #DataSource;
DROP TABLE IF EXISTS #DataSourcePrepared;

CREATE TABLE #DataSource
(
    [ApplicationId] INT
   ,[Key] CHAR(1)
   ,[Value] VARCHAR(12)
   ,[Date] DATETIME2(0)
);

INSERT INTO #DataSource ([ApplicationId], [Key], [Value], [Date])
VALUES (123, 'A', 'abc', '2020-3-1 14:00:01.000')
      ,(123, 'B', 'abd', '2020-3-1 14:00:02.000')
      ,(123, 'C', 'abe', '2020-3-1 14:00:03.000')
      ,(124, 'A', 'abf', '2020-3-1 14:01:00.000')
      ,(124, 'D', 'abg', '2020-3-1 14:01:01.000');


CREATE TABLE #DataSourcePrepared
(
    [ApplicationId] INT
   ,[ColumnName] VARCHAR(32)
   ,[Value] VARCHAR(32)
)

INSERT INTO #DataSourcePrepared ([ApplicationId], [ColumnName], [Value])
SELECT [ApplicationId]
      ,[Key]
      ,[value] 
FROM #DataSource
UNION ALL
SELECT [ApplicationId]
      ,[Key] + '_Date'
      ,CONVERT(VARCHAR(19), [Date], 121)
FROM #DataSource;

DECLARE @DymanimcTSQLSatement NVARCHAR(MAX)
       ,@DynamicColumns NVARCHAR(MAX);

SET @DynamicColumns = STUFF
                      (
                        (
                            SELECT ',' + QUOTENAME([ColumnName])
                            FROM #DataSourcePrepared
                            GROUP BY [ColumnName]
                            ORDER BY [ColumnName]
                            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)')
                        ,1
                        ,1
                        ,''
                     );



SET @DymanimcTSQLSatement = N'
SELECT *
FROM #DataSourcePrepared
PIVOT
(
    MAX([value]) FOR [ColumnName] IN (' + @DynamicColumns +')
) PVT;';

EXECUTE sp_executesql @DymanimcTSQLSatement;

enter image description here

You just need to prepare the data before the actual PIVOT. Also, note that I am ordering the columns when I am building the dynamic part by name. In your real case, you may want to change this to something complex.

user_10803540

you can try this

DECLARE @_cols AS NVARCHAR(MAX) =''
DECLARE @_sql  AS NVARCHAR(MAX)

SELECT 
    @_cols +=','+ QUOTENAME([Key]) + ','  + QUOTENAME([Key]+'_Date') 
FROM 
    (SELECT DISTINCT [Key] FROM ExampleTbl) T

SET @_cols = STUFF(@_cols,1,1,'')

set @_sql = 'SELECT * FROM (
    SELECT ApplicationId, [Key], Value FROM ExampleTbl
    UNION ALL
    SELECT ApplicationId, [Key] + ''_Date'' AS [Key], CONVERT(VARCHAR(30), [Date],121 ) AS Value FROM ExampleTbl
) SRC
    PIVOT (MAX(Value) FOR [Key] IN ('+@_cols +' )) AS PVT';

EXEC(@_sql)

Result:

ApplicationId A       A_Date                      B          B_Date                     C            C_Date                    D       D_Date
------------- ------- --------------------------- ---------- -------------------------- ------------ ------------------------- ------- -------------------------
123           abc     2020-03-01 14:00:01.000     abd        2020-03-01 14:00:02.000    abe          2020-03-01 14:00:03.000   NULL    NULL
124           abf     2020-03-01 14:01:00.000     NULL       NULL                       NULL         NULL                      abg     2020-03-01 14:01:01.000
user_85001100
Related