SQL Server row change to column

Question

I have data in a SQL Server table with the following structure:

TradeDateTime          Strike CallOI
2023-08.02 15.01.00    19500  31015
2023-08.02 15.01.00    19550  2606
2023-08.02 15.01.00    19600  18062
2023-08.02 15.01.00    19650  2553

I want to change as like this

TradeDateTime        19500   19550  19600   19650
2023-08.02 15.01.00  31015   2606   18062   2553

I want change row to column using SQL query. Please help me.

enter image description here

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Aug 03 '23 at 04:53
https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Merci Dieu KIMPOLO, Aug 03 '23 at 05:51

score 1 · Answer 1 · answered Aug 03 '23 at 05:50

You are looking for dynamic T-SQL PIVOT. The idea is simple - build the PIVOT columns using DISTINCT over the [Strike] column values.

CREATE TABLE #DataSource
(
    [TradeDateTime] VARCHAR(32)
   ,[Strike] INT
   ,[CallOI] INT
);

INSERT INTO #DataSource ([TradeDateTime], [Strike], [CallOI])
VALUES ('2023-08.02 15.01.00', 19500, 31015)
      ,('2023-08.02 15.01.00', 19550, 2606)
      ,('2023-08.02 15.01.00', 19600, 18062)
      ,('2023-08.02 15.01.00', 19650, 2553);

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


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

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

EXEC sp_executesql @DynammicTSQLStatement;

DROP TABLE #DataSource;

Thank you very much Brother for your help, It is very good, Excellent. — Chelladurai, Aug 15 '23 at 11:14

SQL Server row change to column

1 Answers1