透视多个表的 SQL 语法

Question

在过去的几天里，我一直在研究这个，并且一直在兜兜转转。

我的问题基于我在这篇文章中接受的答案:stackoverflow question

多亏了 Damir Sudarevic，我现在将我的数据从一个 400 列的表转移到了一个更易于管理的数据库结构。

我的数据库看起来像这样:

 CREATE TABLE JobFiles (
  JobID  UNIQUEIDENTIFIER PRIMARY KEY,
  MachineID UNIQUEIDENTIFIER REFERENCES Machines(MachineID),
  [Desc]  NVARCHAR(MAX),
  Name  NVARCHAR(255),
  JobOpen  BIT,
  [CreateDate]  DATETIME NOT NULL DEFAULT GETDATE(),
  [ModifyDate]  DATETIME NOT NULL DEFAULT GETDATE(),
  [CreatedByUser]  NVARCHAR(64)  DEFAULT '',
  [ModifiedByUser] NVARCHAR(64)  DEFAULT '')
GO 

 CREATE TABLE JobParamType (
  ParamTypeID UNIQUEIDENTIFIER PRIMARY KEY,
  Name  NVARCHAR(255),
  [Desc]  NVARCHAR(MAX),
  IsTrait  NVARCHAR)

GO
 CREATE TABLE JobParamGroup (
  ParamGroupID UNIQUEIDENTIFIER PRIMARY KEY,
  Name  NVARCHAR(255),
  [Desc]  NVARCHAR(MAX))

GO


 CREATE TABLE JobParams (
  ParamID  UNIQUEIDENTIFIER PRIMARY KEY,
  ParamTypeID UNIQUEIDENTIFIER REFERENCES JobParamType(ParamTypeID),
  ParamGroupID UNIQUEIDENTIFIER REFERENCES JobParamGroup(ParamGroupID),
  JobFileID UNIQUEIDENTIFIER REFERENCES JobFiles(JobID),
  IsEnabled BIT)

GO

 -- Text based property
 CREATE TABLE JobTrait (
  ParamID UNIQUEIDENTIFIER PRIMARY KEY REFERENCES JobParams(ParamID),
  Value  NVARCHAR(MAX) )
GO 

 -- Numeric based property 
 CREATE TABLE JobMeasurement (
  ParamID UNIQUEIDENTIFIER PRIMARY KEY REFERENCES JobParams(ParamID),
  Value FLOAT,
  Format NVARCHAR(20),
  Unit NVARCHAR(MAX) )
GO 

但是，对于我的应用程序的特定功能，我需要将每个 JobParamType.Name 行列为包含 JobMeasurement.Value 或 JobTrait.Value 的列作为每个 JobFiles.Name 的数据。

JobParamType.IsTrait 用于确定值是度量值还是特征值。

IE。

JobName  |  ParamName1      |  ParamName2      |  ParamName3       ... | ParamName400
"MyJob"     MesurementValue    TraitValue         MesurementValue  ...   TraitValue  
"TestJob"   MesurementValue    TraitValue         MesurementValue  ...   TraitValue  
"Job2"      MesurementValue    TraitValue         MesurementValue  ...   TraitValue  

etc

我一直在玩数据透视表，并通过查看示例并遵循它们设法从 JobParamType 表中获取列，但现在变得非常复杂，因为我的数据被分成多个表，并且开始让我头疼! !!

DECLARE @cols NVARCHAR(MAX)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 10 PERCENT
                                '],[' + tParams.Name
                        FROM    dbo.JobParamType  AS tParams
                        ORDER BY '],[' + tParams.Name
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'
print @cols

我希望有人可以帮助我进行透视并从多个表中获取数据。

我希望这是有道理的，我期待着您的帮助和讨论。

先谢谢了。

Answer 1

我将发布来自 的一些示例this model ——因为我已经有了。两种模型都非常相似，因此采用这种技术应该不会有太多麻烦。

说到头疼，我发现最简单的方法就是一步一步来，以后再优化。

步骤 1 .
创建一个 View 来展平模型； ( see the model )

CREATE VIEW dbo.vProperties AS 
SELECT  m.MachineID AS [Machine ID]
,s.SetupID AS [Setup ID]
,p.PropertyID AS [Property ID]
,t.PropertyTypeID AS [Property Type ID]
,m.Name AS [Machine Name]
,s.Name AS [Setup Name]
,t.Name AS [Property Type Name]
,t.IsTrait AS [Is Trait]
,x.Value AS [Measurement Value]
,x.Unit AS [Unit]
,y.Value AS [Trait]
FROM dbo.Machine AS m
JOIN dbo.Setup AS s ON s.MachineID = m.MachineID
JOIN dbo.Property AS p ON p.SetupID = s.SetupID
JOIN dbo.PropertyType AS t ON t.PropertyTypeID = p.PropertyTypeID
LEFT JOIN dbo.Measurement AS x ON x.PropertyID = p.PropertyID
LEFT JOIN dbo.Trait AS y ON y.PropertyID = p.PropertyID

步骤 2 .

创建 View 仅生成 [Setup Name], [Property Type Name], [Value] ;请注意，在这一列中，测量值和特征最终在同一列中。您可能会使用 JobName, ParameterTypeName, Value

CREATE VIEW dbo.vSetupValues AS 
SELECT  [Setup Name]
       ,[Property Type Name]
       ,COALESCE(cast([Measurement Value] AS varchar(50)), [Trait]) AS [Val]
FROM dbo.vProperties

步骤 3 .

使用要排序的列创建属性(参数)列表

DECLARE @Props TABLE (
id int IDENTITY (1,1)
,PropName varchar(50)
);

INSERT INTO @Props  (PropName)
SELECT DISTINCT [Name]
FROM dbo.PropertyType

第 4 步 .

现在我将动态创建查询文本

DECLARE @qw TABLE(
id int IDENTITY (1,1)
, txt nchar(500)
)

INSERT  INTO @qw (txt)
  SELECT  'SELECT' UNION
  SELECT  '[Setup Name]' ;

INSERT  INTO @qw (txt)   
  SELECT  ',MAX(CASE [Property Type Name] WHEN ''' + PropName
  + ''' THEN Val ELSE NULL END) AS [' + PropName + ']'
  FROM  @Props
  ORDER BY id;

INSERT  INTO @qw (txt)
 SELECT  'FROM dbo.vSetupValues' UNION
 SELECT  'GROUP BY [Setup Name]' UNION
 SELECT  'ORDER BY [Setup Name]';

步骤 5 .

这是查询的文本，从这一点我可以将其打包成一个存储过程、另一个 View 或一个变量以用作动态sql。

SELECT txt FROM @qw

返回

SELECT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
[Setup Name]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
,MAX(CASE [Property Type Name] WHEN 'Diameter LSL' THEN [Val] ELSE NULL END) AS [Diameter LSL]                                                                                                                                                                                                                                                                                                                                                                                                                      
,MAX(CASE [Property Type Name] WHEN 'Diameter USL' THEN [Val] ELSE NULL END) AS [Diameter USL]                                                                                                                                                                                                                                                                                                                                                                                                                      
,MAX(CASE [Property Type Name] WHEN 'Force LSL' THEN [Val] ELSE NULL END) AS [Force LSL]                                                                                                                                                                                                                                                                                                                                                                                                                            
,MAX(CASE [Property Type Name] WHEN 'Force USL' THEN [Val] ELSE NULL END) AS [Force USL]                                                                                                                                                                                                                                                                                                                                                                                                                            
,MAX(CASE [Property Type Name] WHEN 'Leak LSL' THEN [Val] ELSE NULL END) AS [Leak LSL]                                                                                                                                                                                                                                                                                                                                                                                                                              
,MAX(CASE [Property Type Name] WHEN 'Leak USL' THEN [Val] ELSE NULL END) AS [Leak USL]                                                                                                                                                                                                                                                                                                                                                                                                                              
,MAX(CASE [Property Type Name] WHEN 'Press Travel LSL' THEN [Val] ELSE NULL END) AS [Press Travel LSL]                                                                                                                                                                                                                                                                                                                                                                                                              
,MAX(CASE [Property Type Name] WHEN 'Press Travel USL' THEN [Val] ELSE NULL END) AS [Press Travel USL]                                                                                                                                                                                                                                                                                                                                                                                                              
,MAX(CASE [Property Type Name] WHEN 'Seal Height LSL' THEN [Val] ELSE NULL END) AS [Seal Height LSL]                                                                                                                                                                                                                                                                                                                                                                                                                
,MAX(CASE [Property Type Name] WHEN 'Seal Height USL' THEN [Val] ELSE NULL END) AS [Seal Height USL]                                                                                                                                                                                                                                                                                                                                                                                                                
FROM dbo.vSetupValues                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
GROUP BY [Setup Name]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
ORDER BY [Setup Name]                                      

如果我运行这个:

(来源: damirsystems.com)

更新 :修复了第 4 步中的错误，缺少 max() 并添加了结果示例。