Join Vertical & Horizontal table in SQL Server usi

问题:

I want to join two tables and combine into one but problem is one table is in Horizontal Format and other in Vertical format.

below is the table structure and join will be on Employeeid:

Table 1 : EmpDetail

ID | CODE   | Name
-- |--------| ---
1  | 1008M  | ABC
2  | 1039E  | XYZ
3  | 1040E  | TYS

Table 2 : EmpCustomeDetail

EmpID | FiledName   | FieldValue 
--    |--------     |  ---
1     | FlD1        | temp1
1     | FlD2        | temp2
1     | FlD3        | temp3
2     | FlD1        | temp1
3     | FLD4        | temp6

Desired Output Required :

EmpID |  Code |  Name | Fld1  | Fld2  | Fld3  | Fld4 
--    |----   | ------|  ---  | ----  |----   |----  
1     | 1008M | ABC   | temp1 | temp2 | temp3 | null
2     | 1039E | XYZ   | temp1 | null  | null  | null
3     | 1040E | TYS   | null  | null  | null  | temp6

I had tried using Pivot Query but it is not giving exact output which i requried Below is the query so far i have tried

SELECT A.*
FROM (
    SELECT
        e.Id,
        e.code,
        e.Fname,        
    FROM EmpDetail e  
    LEFT JOIN (
        SELECT *
        FROM (
            SELECT
                  d.CustomeFieldName
                , c.ComboValue
                , d.EmployeeId              
            FROM EmpCustomeDetail d
        ) src 
        PIVOT (
            MAX(FieldValue)          
        ) src2
    ) c ON e.Id = c.EmployeeId       

) A

回答1:

Here are two statements:

The first is a simple PIVOT. You can use it, in case you know all Fieldnames (btw: there's a typo in your sample) in advance.

The second is roughly the same statement, but the column names are taken dynamically. This will work with (almost) any count and with different namings.

First a mock-up-test-scenraio

CREATE TABLE DummyEmpDetail (ID INT,CODE VARCHAR(10),Name VARCHAR(100));
INSERT INTO DummyEmpDetail VALUES
 (1,'1008M','ABC')
,(2,'1039E','XYZ')
,(3,'1040E','TYS');

CREATE TABLE DummyEmpCustomeDetail (EmpID INT,FiledName VARCHAR(100),FieldValue VARCHAR(100)); 
INSERT INTO DummyEmpCustomeDetail VALUES
 (1,'FlD1','temp1')
,(1,'FlD2','temp2')
,(1,'FlD3','temp3')
,(2,'FlD1','temp1')
,(3,'FLD4','temp6');

--The static PIVOT statement

SELECT p.EmpID
      ,p.Name
      ,p.CODE
      ,p.Fld1
      ,p.Fld2
      ,p.Fld3
      p,Fld4
FROM
(
    SELECT e.CODE,e.Name,ec.*
    FROM DummyEmpDetail AS e
    INNER JOIN DummyEmpCustomeDetail AS ec ON e.ID=ec.EmpID
) AS tbl
PIVOT
(
    MAX(FieldValue) FOR FiledName IN(Fld1,Fld2,Fld3,Fld4)
) AS p;

--The dynamic PIVOT statement

DECLARE @colNames VARCHAR(MAX)=
(
    STUFF
    (
        (
            SELECT DISTINCT ',' + QUOTENAME(FiledName) FROM DummyEmpCustomeDetail
            FOR XML PATH('')
        ),1,1,''
    )   
);
DECLARE @command VARCHAR(MAX)=
'SELECT p.EmpID
      ,p.Name
      ,p.CODE
      ,' + @colNames + 
' FROM
(
    SELECT e.CODE,e.Name,ec.*
    FROM DummyEmpDetail AS e
    INNER JOIN DummyEmpCustomeDetail AS ec ON e.ID=ec.EmpID
) AS tbl
PIVOT
(
    MAX(FieldValue) FOR FiledName IN(' + @colnames + ')
) AS p;';

EXEC (@command);
GO

DROP TABLE DummyEmpCustomeDetail;
DROP TABLE DummyEmpDetail;

Both lead to the same result...

回答2:

Try like below. If values of fieldname will not be static then you should use dynamic sql.

 SELECT EMPID,
           CODE,
           NAME,
           FLD1,
           FLD2,
           FLD3,
           FLD4
    FROM   EmpDetail C
           JOIN (SELECT A.*
                 FROM   EmpCustomeDetail
                        PIVOT ( MIN([FIELDVALUE])
                              FOR [FILEDNAME] IN([FLD1],
                                                 [FLD3],
                                                 [FLD2],
                                                 FLD4) )A)B
             ON C.ID = B.[EMPID] 

回答3:

No need for sub-queries.

select  e.*,FlD1,FlD2,FlD3,FlD4 
from    EmpDetail e
        left join EmpCustomeDetail 
                      pivot (max(FieldValue) for FiledName in (FlD1,FlD2,FlD3,FlD4)) ecd
        on ecd.EmpID = e.ID

---

+----+-------+------+-------+-------+-------+-------+
| ID | CODE  | Name | FlD1  | FlD2  | FlD3  | FlD4  |
+----+-------+------+-------+-------+-------+-------+
| 1  | 1008M | ABC  | temp1 | temp2 | temp3 | NULL  |
+----+-------+------+-------+-------+-------+-------+
| 2  | 1039E | XYZ  | temp1 | NULL  | NULL  | NULL  |
+----+-------+------+-------+-------+-------+-------+
| 3  | 1040E | TYS  | NULL  | NULL  | NULL  | temp6 |
+----+-------+------+-------+-------+-------+-------+