时间戳

首页 > 解决方案 > 在 SQL 中将多列转换为一列但多行

sql - 在 SQL 中将多列转换为一列但多行

问题描述

我有以下表结构：

cod_chestionar  IT        HR    data_lansare
3GDH            9.83    9.32    6.12.2017
4XW6            9.14    9.89    6.11.2017
5Y7R            9       10      10.11.2017
DGVR            9.05    9.9     6.12.2017

我想获得以下内容：

cod_chestionar  dep nota    an     luna data_lansare    
3GDH             IT 9.83    2017    12  6.12.2017   
4XW6             IT 9.14    2017    11  6.11.2017   
5Y7R             IT    9    2017    11  10.11.2017  
DGVR             IT 9.05    2017    12  6.12.2017   
3GDH             HR 9.32    2017    12  6.12.2017
4XW6             HR 9.89    2017    11  6.11.2017
5Y7R             HR   10    2017    11  10.11.2017
DGVR             HR  9.9    2017    12  6.12.2017

我获取第一种格式的查询是：

SELECT DISTINCT
       Cod_Chestionar,
       ROUND(AVG(CAST(Intrebare1 AS FLOAT)), 2) AS It,
       ROUND(AVG(CAST(Intrebare2 AS FLOAT)), 2) AS Hr,
       CAST(DAY(Data_Introducere) AS VARCHAR)+'.'+CAST(MONTH(Data_Introducere) AS VARCHAR)+'.'+CAST(YEAR(Data_Introducere) AS VARCHAR) AS Data_Lansare
FROM Personal AS P,
     Suport_Depart,
     Email_Suport AS E
WHERE E.Cod_Email = Suport_Depart.Cod
      AND E.Email = P.Email
      AND Intrebare1 != 'Nu interactionez'
      AND Intrebare2 != 'Nu interactionez'
GROUP BY Cod_Chestionar,
         Data_Introducere;

请不要介意旧的sql格式，我写的很着急。任何建议将不胜感激。

标签： sqlsql-server

解决方案

SELECT DISTINCT
       Cod_Chestionar,
       Dep = 'IT',
       Nota = ROUND(AVG(CAST(Intrebare1 AS FLOAT)), 2),
       Luna = MONTH(Data_Introducere),
       CAST(DAY(Data_Introducere) AS VARCHAR)+'.'+CAST(MONTH(Data_Introducere) AS VARCHAR)+'.'+CAST(YEAR(Data_Introducere) AS VARCHAR) AS Data_Lansare
FROM Personal AS P,
     Suport_Depart,
     Email_Suport AS E
WHERE E.Cod_Email = Suport_Depart.Cod
      AND E.Email = P.Email
      AND Intrebare1 != 'Nu interactionez'
      AND Intrebare2 != 'Nu interactionez'
GROUP BY Cod_Chestionar,
         Data_Introducere
UNION ALL
SELECT DISTINCT
       Cod_Chestionar,
       Dep = 'HR',
       Nota = ROUND(AVG(CAST(Intrebare2 AS FLOAT)), 2),
       Luna = MONTH(Data_Introducere),
       CAST(DAY(Data_Introducere) AS VARCHAR)+'.'+CAST(MONTH(Data_Introducere) AS VARCHAR)+'.'+CAST(YEAR(Data_Introducere) AS VARCHAR) AS Data_Lansare
FROM Personal AS P,
     Suport_Depart,
     Email_Suport AS E
WHERE E.Cod_Email = Suport_Depart.Cod
      AND E.Email = P.Email
      AND Intrebare1 != 'Nu interactionez'
      AND Intrebare2 != 'Nu interactionez'
GROUP BY Cod_Chestionar,
         Data_Introducere;

推荐阅读