透视多个表的SQL语法 | 透视多个表的SQL语法

本文介绍了透视多个表的SQL语法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

过去的几天我一直在为此工作，并且一直在圈子里逛逛.

我的问题基于我在本文中接受的答案:

现在，由于达米尔·苏达雷维奇(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用于确定值是度量还是特征.

即

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

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

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

谢谢你.

解决方案

我将发布 -因为我已经有了它们.两种模型都非常相似，因此采用这种技术应该不会有太多麻烦.

当涉及到头痛时，我发现最简单的方法是逐步进行并在以后进行优化.

第1步.
创建一个视图以展平模型； ( )

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步.

创建一个视图，仅生成[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步.

这是查询的文本，从这一点出发，我可以将其打包到存储过程，另一个视图或变量中，以用作动态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]

如果我运行此代码:

更新:修复了第4步的错误，该错误缺少max()并添加了示例示例.

I have spent the past couple of days working on this and am going around in circles.

My question is based around the answer I accepted in this post: stackoverflow question

I now have my data moved from a single 400 column table to a much more managable database structure with many thanks to Damir Sudarevic.

My database looks like this:

 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

However, for a particular function of my application I need to list every JobParamType.Name row as columns containing a JobMeasurement.Value or JobTrait.Value as its data for each JobFiles.Name.

JobParamType.IsTrait is used to determine if a value is a Measurement or Trait.

i.e.

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

etc

I have been playing with pivoting tables and have managed to get the columns from the JobParamType table by looking at examples and following them but it is now getting quite complicated because my data is split between several tables and it is starting to make my head hurt!!!

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

I am hoping someone could help me with the pivoting and getting the data from multiple tables.

I hope this makes sense and I look forward to your help and discussions.

Thank you in advanced.

解决方案

I will post a few examples from this model -- because I already have them. Both models are very similar, so you should not have too much trouble adopting this technique.

When it comes to headache, I find that the simplest way is to go step by step, and optimize later.

Step 1.
Create a view to flatten the model; (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

Step 2.

Create a view to generate only [Setup Name], [Property Type Name], [Value]; note that in this one the measurement value and trait end up in the same column. You would probably use 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

Step 3.

Create list of properties (Parameters) with a column to order by

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

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

Step 4.

Now I will dynamically create the query text

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]';

Step 5.

And here is the text of the query, form this point I can package this into a stored procedure, another view, or into a variable to use as dynamic sql.

SELECT txt FROM @qw

returns

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]

And if I run this:

UPDATE: fixed bug at step 4, was missing max() and added results example.

这篇关于透视多个表的SQL语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！