[Solved] T-SQL How to rotate table using simplest way [closed]

Introduction

T-SQL (Transact-SQL) is a powerful language used to query and manipulate data in a Microsoft SQL Server database. It is a powerful language that allows you to perform complex operations on data stored in a database. One of the most common operations is to rotate a table, which is the process of rearranging the columns and rows of a table. In this tutorial, we will discuss the simplest way to rotate a table using T-SQL.

Solution

The simplest way to rotate a table in T-SQL is to use the PIVOT operator. This operator allows you to transform the rows of a table into columns.

For example, if you have a table with the following data:

ID | Name | Value
1 | A | 10
2 | B | 20
3 | C | 30

You can use the PIVOT operator to rotate the table and get the following result:

Name | 1 | 2 | 3
A | 10 | 0 | 0
B | 0 | 20 | 0
C | 0 | 0 | 30

The following query can be used to achieve this result:

SELECT *
FROM
(SELECT ID, Name, Value FROM table) AS SourceTable
PIVOT
(SUM(Value) FOR ID IN ([1], [2], [3])) AS PivotTable;

SELECT * 
INTO MyTable
FROM
    (SELECT
         'C1' AS [c_id], '1' AS [a1], '2' AS [a2], '1.1' AS [a3], '1.4' AS [a4]
     UNION All
     SELECT 'C2' AS [c_id], 'Ann' AS [a1], 'Jane' AS [a2], 'Andrew' AS [a3], 'John' AS [a4]
     UNION All
     SELECT 'C3' AS [c_id], '24323' AS [a1], '4323' AS [a2], '5542' AS [a3], '45543' AS [a4]
) r;

CREATE TABLE #tempTable
(
    [Transpose] NVARCHAR(128),
    C1 NVARCHAR(MAX),
    C2 NVARCHAR(MAX),
    C3 NVARCHAR(MAX)
)

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

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(C_id)
                      FROM MyTable
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SELECT @crossApplyCols = STUFF((SELECT ',' + CHAR(10) + CHAR(9) + '(''' + c.name + ''', ' + QUOTENAME(c.name) + ')'
                                FROM sys.columns c
                                WHERE c.object_id = OBJECT_ID('MyTable') AND c.name <> 'c_id'
                                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = 'INSERT INTO #tempTable
              SELECT [Transpose], ' + @cols + '
              FROM
              (
                  SELECT C_id, a AS [Transpose], value
                  FROM MyTable
                  CROSS APPLY
                  (
                      VALUES
                          ' + @crossApplyCols + '
                  ) AS ca (a, value)
              ) AS src
              PIVOT
              (
                  MAX(value) FOR C_id IN (' + @cols + ')
              ) AS pvt'

EXEC sp_executesql @query

SELECT * FROM #tempTable

Select *
 From (
        Select A.cid
              ,B.* 
         From #tmptab A
         Cross Apply  (
                         Select [Key]
                               ,Value
                         From OpenJson(  (Select A.* For JSON Path,Without_Array_Wrapper )  ) 
                         Where [Key] not in ('cid')
                      ) B
      ) src
 Pivot ( max(Value) for cid in ([C1],[C2],[C3] ) ) pvt

Rotating a table in T-SQL is a relatively simple process. The easiest way to do this is to use the PIVOT operator. This operator allows you to take a table and rotate it so that the columns become the rows and the rows become the columns. To use the PIVOT operator, you must specify the columns that you want to rotate, the values that you want to use for the new columns, and the aggregate function that you want to use to generate the values for the new columns.

For example, let’s say you have a table with the following columns: ID, Name, and Value. You want to rotate this table so that the Name column becomes the rows and the Value column becomes the columns. You can do this using the following query:

SELECT *
FROM 
    (SELECT ID, Name, Value
     FROM YourTable) AS SourceTable
PIVOT
(
    SUM(Value)
    FOR Name IN ([Column1], [Column2], [Column3])
) AS PivotTable

In this query, the columns that you want to rotate are specified in the FOR clause. The aggregate function that you want to use to generate the values for the new columns is specified in the SUM clause. The result of this query will be a table with the ID column as the rows and the Value column as the columns.

You can also use the PIVOT operator to rotate a table with multiple columns. For example, let’s say you have a table with the following columns: ID, Name, Value1, and Value2. You want to rotate this table so that the Name column becomes the rows and the Value1 and Value2 columns become the columns. You can do this using the following query:

SELECT *
FROM 
    (SELECT ID, Name, Value1, Value2
     FROM YourTable) AS SourceTable
PIVOT
(
    SUM(Value1 + Value2)
    FOR Name IN ([Column1], [Column2], [Column3])
) AS PivotTable

In this query, the columns that you want to rotate are specified in the FOR clause. The aggregate function that you want to use to generate the values for the new columns is specified in the SUM clause. The result of this query will be a table with the ID column as the rows and the Value1 and Value2 columns as the columns.

Using the PIVOT operator is the simplest way to rotate a table in T-SQL. It allows you to quickly and easily rotate a table with multiple columns. However, it is important to note that the PIVOT operator is not supported in all versions of SQL Server. If you are using an older version of SQL Server, you may need to use other methods to rotate a table.