Home > Database > Mysql Tutorial > How to Transpose a SQL Table with Multiple Columns?

How to Transpose a SQL Table with Multiple Columns?

DDD
Release: 2025-01-14 20:52:47
Original
334 people have browsed it

How to Transpose a SQL Table with Multiple Columns?

Transpose a SQL table with multiple columns

Question:

You need to transpose a SQL table with multiple columns, for example:

1

2

3

4

5

6

7

<code>Day  A  B

---------

Mon  1  2

Tue  3  4

Wed  5  6

Thu  7  8

Fri  9  0</code>

Copy after login

Transpose to the following format:

1

2

3

4

<code>Value Mon Tue Wed Thu Fri

--------------------------

A      1   3   5   7   9

B      2   4   6   8   0</code>

Copy after login

Solution:

To transpose a table with multiple columns, you can use the UNPIVOT and PIVOT functions together.

  1. UNPIVOT: Convert multiple columns (A, B) into rows and add a column for column names:

1

2

3

4

5

6

7

<code class="language-sql">select day, col, value

from yourtable

unpivot

(

  value

  for col in (A, B)

) unpiv</code>

Copy after login
  1. PIVOT: Convert "day" value to column and aggregate "value" column:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

<code class="language-sql">select *

from

(

  select day, col, value

  from yourtable

  unpivot

  (

    value

    for col in (A, B)

  ) unpiv

) src

pivot

(

  max(value)

  for day in (Mon, Tue, Wed, Thu, Fri)

) piv</code>

Copy after login

This will generate the required transposed table.

Additional notes:

  • If your SQL Server version is 2008 or higher, you can use CROSS APPLY and VALUES instead of the UNPIVOT function to transpose the data:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

<code class="language-sql">select *

from

(

  select day, col, value

  from yourtable

  cross apply

  (

    values ('A', ACalls), ('B', BCalls)

  ) c (col, value)

) src

pivot

(

  max(value)

  for day in (Mon, Tue, Wed, Thu, Fri)

) piv</code>

Copy after login
  • To perform the transpose operation on your specific query, you can use code similar to the following structure:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

<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>

Copy after login

The above is the detailed content of How to Transpose a SQL Table with Multiple Columns?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template