convert multiple columns to one column but multiple rows in SQL

Question

I have the following table structure:

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

And I would want to obtain the following:

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

My query to obtain the first format is:

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;

Please don't mind the old sql format, I wrote it on a hurry. Any suggestion would be appreciated.

Hi and welcome to SO. You really should consider using ANSI-92 style joins. They have been available for more than 25 years now. sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins And don't be scared to use some white space and formatting in your code so you can read it. — Sean Lange
– Sean Lange, Commented Sep 4, 2018 at 13:29

www.StudioSQL.nl · Accepted Answer · 2018-09-04 16:47:04Z

1

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;

edited Sep 4, 2018 at 16:47

www.StudioSQL.nl

1358 bronze badges

answered Sep 4, 2018 at 13:27

Serkan Ekşioğlu

3856 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rares Biris Over a year ago

thanks..just what I need. I'll accept your answer as soon as I can.

Sean Lange Over a year ago

Some formatting, proper joins and explanation this could be a great answer.

Yogesh Sharma · Accepted Answer · 2018-09-04 13:27:42Z

0

You can use apply :

select cod_chestionar, tt.dep, tt.depv as nota, year(data_lansare) as an, month(data_lansare) as luna, data_lansare 
from table t cross apply
     ( values ('IT', IT), ('HR', HR)  
     ) tt(dep, depv);

answered Sep 4, 2018 at 13:27

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

convert multiple columns to one column but multiple rows in SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related