根据其中一个表的值从两个表中进行选择

1) Table1 say table1 with structure as :

1）表1表示table1，结构如下：

moduleID | moduleName 10 | XYZ 20 | PQR 30 | ABC

moduleID | moduleName 10 | XYZ 20 | PQR 30 | ABC

2) Table2 say table2 with structure as :

2）表2说table2结构为：

moduleID | Level | Value 10 | 1 | 20 10 | 2 | 30 30 | 3 | 40 10 | 3 | 50 20 | 2 | 30 moduleID being primary key in table1,and value of the column level can have values 1 to 3.
Now it is required to display the data as follows :

moduleID |等级|价值10 | 1 | 20 10 | 2 | 30 30 | 3 | 40 10 | 3 | 50 20 | 2 | 30 moduleID是table1中的主键，列级别的值可以是1到3的值。现在需要显示如下数据：

moduleID | moduleName | Level1 | Level2 | Level3 10 | XYZ | 20 | 30 | 50 20 | PQR | NULL | 30 | NULL 30 | ABC | NULL | NULL | 50
In simpler terms, values of column Level in table2 is displayed as Level1, Level2 and Level3 and values corresponding to each level is populated in the corresponding moduleID row. Any help on this? beginner here in SQL. Something to do with Views?

moduleID | moduleName | Level1 | Level2 | Level3 10 | XYZ | 20 | 30 | 50 20 | PQR | NULL | 30 | NULL 30 | ABC | NULL | NULL | 50简单来说，table2中列Level的值显示为Level1，Level2和Level3，并且对应于每个级别的值将填充在相应的moduleID行中。对此有何帮助？在SQL中初学者。与视图有关？

3 个解决方案

#1

You can use conditional aggregation:

您可以使用条件聚合：

select t1.moduleID, t1.moduleName,
       MAX(CASE WHEN Level = 1 THEN Value END) Level1,
       MAX(CASE WHEN Level = 2 THEN Value END) Level2,
       MAX(CASE WHEN Level = 3 THEN Value END) Level3
from table1 as t1
left join table2 as t2 on t1.moduleID = t2.moduleID
group by t1.moduleID, t1.moduleName

#2

Do a LEFT JOIN for each Level in table 2.

为表2中的每个级别执行LEFT JOIN。

select t1.*, t2_l1.value as Level1, t2_l2.value as Level2, t2_l3.value as Level3
from table1 t1
left join table2 t2_l1 on t1.moduleID = t2_l1.moduleID and t2_l1.Level = 'Level1'
left join table2 t2_l2 on t1.moduleID = t2_l2.moduleID and t2_l2.Level = 'Level2'
left join table2 t2_l3 on t1.moduleID = t2_l3.moduleID and t2_l3.Level = 'Level3'

If a moduleID has several different values for a level, all of them will be returned. (If you want the sum instead, take a look at Giorgos Betsos' answer.)

如果moduleID具有多个不同的级别值，则将返回所有这些值。（如果你想要总和，请看看Giorgos Betsos的回答。）

#3

Refer this all process it will work fine for your expected answer.

请参阅此过程，它将适用于您的预期答案。

CREATE TABLE Table1
(moduleName VARCHAR(50),moduleID INT)
GO
--Populate Sample records
INSERT INTO Table1 VALUES('.NET',10)
INSERT INTO Table1 VALUES('Java',20)
INSERT INTO Table1 VALUES('SQL',30)

CREATE TABLE Table2
(moduleID INT,[Level] INT,Value INT)
GO
--Populate Sample records
INSERT INTO Table2 VALUES(10,1,20)
INSERT INTO Table2 VALUES(10,2,30)
INSERT INTO Table2 VALUES(30,3,40)
INSERT INTO Table2 VALUES(10,3,50)
INSERT INTO Table2 VALUES(20,2,30)
INSERT INTO Table2 VALUES(20,4,60)
GO

CREATE VIEW [dbo].[vw_tabledata]
AS
SELECT t1.[moduleID],[moduleName]
      ,[Level]
      ,[Value]
  FROM [db_Sample].[dbo].[Table2] t2 inner join [db_Sample].[dbo].[Table1] t1 on t1.[moduleID] = t2.[moduleID]
GO







DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([Level])
FROM (SELECT DISTINCT [Level] FROM Table2) AS [Level]

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT moduleID,moduleName, ' + @ColumnName + '
    FROM [vw_tabledata] 
    PIVOT(MAX(Value) 
          FOR [Level] IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

#1