want to add columns in result from existing columns

Question

TABLE Structure

TS                 |    Description    |    Value
2023-02-28 10:42        CLAF                 172
2023-02-28 10:42        CLAF3                119
2023-02-28 10:42        CLAF6                114
2023-02-28 10:42        CLAF8                193
2023-02-28 10:42        CLAF9                163
2023-02-28 10:42        CLAF1                132
2023-02-28 10:43        CLAF                 88
2023-02-28 10:43        CLAF3                93
2023-02-28 10:43        CLAF6                79
2023-02-28 10:43        CLAF8                153
2023-02-28 10:43        CLAF9                109
2023-02-28 10:43        CLAF1                125

I tried below query but in my case the description value can be any. how can I compare Description with any unique value present in column?

SELECT TS,
MAX(CASE WHEN Description ='CLAF' THEN Value END) AS "CLAF"
FROM TrendTable
GROUP BY TS
Order By TS

If any unique value present in Description column Group By TS:

Expected Output:

TS                 |    CLAF    |    CLAF3    |    CLAF6    | CLAF8   |   CLAF9   |   CLAF1
2023-02-28 10:42        172            119          114        193        163         132
2023-02-28 10:43        88             93           79         153        109         125

I hope this will help you to understood, Any help is appreciated

gotqn · Accepted Answer · 2023-03-06T11:40:46.020

You can create a dynamic PIVOT:

CREATE TABLE #DataSource 
(
     [TS] DATETIME
    ,[Description] VARCHAR(50)
    ,[Value] INT
);

INSERT INTO #DataSource ([TS], [Description], [Value])
VALUES ('2023-02-28 10:42', 'CLAF', 172)
      ,('2023-02-28 10:42', 'CLAF3', 119)
      ,('2023-02-28 10:42', 'CLAF6', 114)
      ,('2023-02-28 10:42', 'CLAF8', 193)
      ,('2023-02-28 10:42', 'CLAF9', 163)
      ,('2023-02-28 10:42', 'CLAF1', 132)
      ,('2023-02-28 10:43', 'CLAF', 88)
      ,('2023-02-28 10:43', 'CLAF3', 93)
      ,('2023-02-28 10:43', 'CLAF6', 79)
      ,('2023-02-28 10:43', 'CLAF8', 153)
      ,('2023-02-28 10:43', 'CLAF9', 109)
      ,('2023-02-28 10:43', 'CLAF1', 125);

DECLARE @DynammicTSQLStatement NVARCHAR(MAX)
       ,@DynamicPIVOTColumns NVARCHAR(MAX);


SET @DynamicPIVOTColumns = STUFF
                          (
                                (
                                    SELECT ',' + QUOTENAME([Description]) 
                                    FROM #DataSource
                                    GROUP BY [Description]
                                    ORDER BY [Description]
                                    FOR XML PATH('') ,TYPE
                                ).value('.', 'VARCHAR(MAX)')
                                ,1
                                ,1
                                ,''
                          );

SET @DynammicTSQLStatement = N'
SELECT *
FROM #DataSource
PIVOT
(
    MAX([Value]) FOR [Description] IN (' + @DynamicPIVOTColumns + ')
) PVT';

EXEC sp_executesql @DynammicTSQLStatement;

DROP TABLE #DataSource;

want to add columns in result from existing columns

1 Answers1