如何将列转换为ms SQL Server中的行 [英] How to convert columns to rows in ms SQL server

问题描述

我有一张桌子

TIMETABLE

它包含vales

it contains vales as

id| Day | p1 | p2 | p3 | p4| Short_By_Name_by_Class 
1 | mon | ES | GS | CP | GN| name1
2 | tue | AB | AC | AD | AE| name2
3 | Wed | BA | BB | BC | BD| name3

现在如何转换这个如

Now how to convert this like

PD |mon| tue| wed 
P1 |ES | AB | BA | 
P2 |GS | AC | BB | 
P3 |CP | AD | BC | 
P4 |GN | AE | BD | 

我正在使用此查询



我有什么试过：

I am using this query

What I have tried:

SELECT [DAY], [MON],[TUES],[WED]FROM TIMETABLE WHERE Short_By_Name_by_Class='name1'
unpivot
(ID 
 for [DAY] in ([MON],[TUES],[WED])
 ) unpiv;

推荐答案

首先，你必须 unpivot [ ^ ] data { p1，p2， p3，p4 }到 PD 和 PDValue 字段然后再次转动它们 - 在日字段。

First of all, you have to unpivot[^] data {p1, p2, p3, p4} to PD and PDValue fields then pivot them again - on Day field.

SELECT PD, [mon], [tue], [wed], ...
FROM (
    SELECT id, Day, PD, PDValue 
    FROM (
            SELECT id, Day, p1, p2, p3, p4  
            FROM TIMETABLE
        ) pvtData
    UNPIVOT (PDValue FOR PD IN (p1, p2, p3, p4))
) AS unpvtData
PIVOT (PDValue FOR Day IN ([mon], [tue], [wed], ...)) AS Final

注意：未经测试！

这篇关于如何将列转换为ms SQL Server中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！