c# - 使用动态数据透视查询连接两个表

Question

我想连接两个表并将其合并为一个，但问题是一个表是水平格式，另一个是垂直格式

下面是表结构表 1:

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

表 2:

EmpID |  FieldName |  Value
--    |----        | ------
1     | FH         | 1000  
1     | FB         | 1220  
2     | FHRA       | 3000  
2     | FB         | 3000  
3     | FB         | 3000  

期望的输出:

EmpID |  Code |  Name | Fld1  | Fld2  | Fld3  | Fld4 | FH  | FB | FHRA
--    |----   | ------|  ---  | ----  |----   |----  | --- |--- | ----
1     | 1008M | ABC   | temp1 | temp2 | temp3 | null |1000 |1210| 0  
2     | 1039E | XYZ   | temp1 | null  | null  | null |0    |3000| 3000   
3     | 1040E | TYS   | null  | null  | null  | temp6|0    |3000| 0   

我曾尝试使用 Pivot 查询，但它没有按预期工作。

Answer 1

你必须使用下面的动态查询，你可以通过添加更多的 FieldNames 来测试

CREATE TABLE #table1(EmpID INT,  
    Code VARCHAR(20),  
    Name VARCHAR(20), 
    Fld1 VARCHAR(20), 
    Fld2  VARCHAR(20), 
    Fld3  VARCHAR(20), 
    Fld4 VARCHAR(20))

INSERT INTO #table1 VALUES 
(1,     '1008M','ABC','temp1','temp2','temp3',NULL),
(2,     '1039E','XYZ','temp1',NULL,NULL,null),
(3,     '1040E','TYS',null,NULL,NULL,'temp6')

CREATE TABLE #table2(EmpID INT,  FieldName VARCHAR(20),  VALUE INT)
INSERT INTO #table2 VALUES 
(1,'FH',1000),  
(1,'FB',1220),  
(2,'FHRA',3000),  
(2,'FB',3000),  
(3,'FB',3000)

DECLARE @col VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)

SELECT @col = COALESCE(@col + ', ','') + QUOTENAME(FieldName)
FROM #table2 GROUP BY FieldName

SELECT @col -- This gives: [FB], [FH], [FHRA]

-- Now setting this @col variable in the Dynamic SQL.
SET @sql = '
select EmpID, Code, Name,Fld1,Fld2,Fld3,Fld4, ' + @col + '
from (select a.EmpID, Code, Name,Fld1,Fld2,Fld3,Fld4, b.FieldName, b.value
        from #table1 a 
        join #table2 b on a.empid=b.empid)p
PIVOT(MAX (VALUE) FOR FieldName IN ( ' + @col + ' )
) AS pvt
'

PRINT @sql
EXEC (@sql)

输出:

EmpID   Code    Name    Fld1    Fld2    Fld3    Fld4    FB      FH      FHRA
1       1008M   ABC     temp1   temp2   temp3   NULL    1220    1000    NULL
2       1039E   XYZ     temp1   NULL    NULL    NULL    3000    NULL    3000
3       1040E   TYS     NULL    NULL    NULL    temp6   3000    NULL    NULL