用于透视多个表的SQL语法

过去几天我一直在研究这个问题并且正在进行.

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

我现在将我的数据从单个400列表移动到更加可管理的数据库结构,非常感谢Damir Sudarevic.

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

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,IsTrait  NVARCHAR)
 
GO
 CREATE TABLE JobParamGroup (
  ParamGroupID UNIQUEIDENTIFIER PRIMARY KEY,[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用于确定值是否为Measurement或Trait.

即

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

我希望有人可以帮我转动并从多个表中获取数据.

我希望这是有道理的,我期待着你的帮助和讨论.

先谢谢你.

解决方法

我将在 this model发布一些例子 – 因为我已经有了它们.两种型号都非常相似,因此采用这种技术不会太麻烦.

当谈到头痛时,我发现最简单的方法是逐步进行,然后进行优化.

步骤1.
创建视图以展平模型; (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步.

创建一个视图,仅生成[设置名称],[属性类型名称],[值];请注意,在此测量值和特征最终在同一列中.您可能会使用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,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步.

这里是查询的文本,从这一点开始我可以将它打包成一个存储过程,另一个视图,或者变成一个用作动态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]

如果我运行这个：
alt text http://www.damirsystems.com/dp_images/machinesetup_results.png

更新：修复了第4步的错误,缺少max()并添加了结果示例.

用于透视多个表的SQL语法

解决方法

猜你在找的MsSQL相关文章