0

I'm having a problem with a SQL statement. I would like to show some data from some tables; this is my SQL Diagram:

Database diagram

I would like to show some information from the table called "Signatures", I would like to show in on row the columns: "IdSupervisor,IdManager,IdSupervisor2,IdManager2") And below this info, i would like to show these columns: "FirmaS1,FirmaG1,FirmaS2,FirmaG2).

I've searched on the web with no success.

This is the SQL Query that I made to achieve it in two different statements, but I want to have the two queries in one.

Query 1:

SELECT p1.Name as 'Supervisor', coalesce(p2.Name,'Unnecessary') as 'Manager', coalesce(p3.Name,'Unnecesary') as 'New Supervisor', coalesce(p4.Name,'Unnecesary') as 'New Manager' 
FROM Signatures s1
LEFT JOIN PersonalDepartment dp ON s1.IdSupervisor = dp.IdPersonalDepartment
LEFT JOIN Personal p1 ON dp.IdPersonal = p1.IdPersonal
LEFT JOIN PersonalDepartment dp2 ON s1.IdManager = dp2.IdPersonalDepartment
LEFT JOIN Personal p2 ON dp2.IdPersonal = p2.IdPersonal
LEFT JOIN PersonalDepartment dp3 ON s1.IdSupervisor2 = dp3.IdPersonalDepartment
LEFT JOIN Personal p3 ON dp3.IdPersonal = p3.IdPersonal
LEFT JOIN PersonalDepartment dp4 ON s1.IdManager2 = dp4.IdPersonalDepartment
LEFT JOIN Personal p4 ON dp4.IdPersonal = p4.IdPersonal WHERE IdRequest = 64;

Query 2:

SELECT FirmaS1, FirmaG1, FirmaG1, FirmaG2 FROM Signatures WHERE IdRequest = 64;

Result:

Result of the two querys.

JakeParis
  • 11,056
  • 3
  • 42
  • 65
Machiaveli
  • 27
  • 1
  • 11
  • 1
    The [PIVOT](https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) operator should give you what you need. Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – PinnyM Aug 02 '17 at 18:21
  • What are you expecting for output? – Sean Lange Aug 02 '17 at 18:44
  • Please provide your sample data and expected result. – Ferdinand Gaspar Aug 02 '17 at 19:00

0 Answers0