How Can I Transpose a Full Table in MS SQL Using UNPIVOT and PIVOT?

MS SQL Server: Complete Table Transposition with UNPIVOT and PIVOT

Transposing a table with multiple columns in MS SQL Server requires a combined approach using the UNPIVOT and PIVOT functions. UNPIVOT reshapes multiple columns into rows, while PIVOT converts row values back into columns.

Step-by-Step Guide:

1. Data Unpivoting:

   This step transforms your column data into rows. Replace yourtable, A, B, value, and col with your actual table and column names.

   <code class="language-sql">SELECT day, col, value
   FROM yourtable
   UNPIVOT
   (
       value
       FOR col IN (A, B)
   ) unpiv</code>

2. Data Pivoting:

   Here, the row data is pivoted back into columns. Adjust Mon, Tue, Wed, Thu, Fri to match your specific column names (days of the week in this example).

   <code class="language-sql">SELECT *
   FROM (SELECT * FROM ...) src  -- Replace ... with the UNPIVOT query from step 1
   PIVOT
   (
       MAX(value)
       FOR day IN (Mon, Tue, Wed, Thu, Fri)
   ) piv</code>

3. Integration into Existing Queries:

   To transpose data within an existing query, incorporate the UNPIVOT and PIVOT operations:

   <code class="language-sql">SELECT *
   FROM
   (
       SELECT LEFT(DATENAME(dw, datetime), 3) AS DateWeek,
              col,
              value
       FROM DataTable
       CROSS APPLY
       (
           VALUES ('A', ACalls), ('B', BCalls)
       ) c (col, value)
   ) src
   PIVOT
   (
       SUM(value)
       FOR DateWeek IN (Mon, Tue, Wed, Thu, Fri)
   ) piv</code>

Example using PIVOT and CROSS APPLY (SQL Server 2008 and later):

This example leverages CROSS APPLY for a more concise solution.

<code class="language-sql">SELECT day, col, value
FROM yourtable
CROSS APPLY
(
    VALUES ('A', ACalls), ('B', BCalls)
) c (col, value)
PIVOT
(
    MAX(value)
    FOR day IN (Mon, Tue, Wed, Thu, Fri)
) piv</code>

Important Considerations:

• The col column in the unpivoted result displays the original column names.
• The MAX() function can be replaced with other aggregate functions (like SUM(), AVG(), etc.) based on your data requirements.

This comprehensive guide demonstrates how to effectively transpose entire tables in MS SQL Server, offering flexibility and adaptability for various data manipulation scenarios.

The above is the detailed content of How Can I Transpose a Full Table in MS SQL Using UNPIVOT and PIVOT?. For more information, please follow other related articles on the PHP Chinese website!