SQL syntax for spreading multiple tables

Question

I spent the last couple of days working on this and am going in circles.

My question is based on the answer I accepted in this post: process_stack question

Now I have data moved from one 400 column table to a much more manageable database structure, which is why Damir Sudarevich.

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 the specific function of my application, I need to specify each row of JobParamType.Name as columns containing JobMeasurement.Value or JobTrait.Value as its own data for each JobFiles.Name.

JobParamType.IsTrait is used to determine if a value is a value or attribute.

i.e.

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

I played with pivot tables and got the columns from the JobParamType table, looking at the examples and following their example, but now it is getting quite complicated because my data is shared between several tables and it starts 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 hope someone can help me with turning and getting data from multiple tables.

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

Thank you for the advanced.

Answer 1

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

When it comes to headaches, I find that the easiest way is to go step by step and then optimize.

Step 1
Create a view to smooth the model; ( see 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 dimension, the value and the attribute are in the same column. You probably JobName, ParameterTypeName, Value 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 a list of properties (parameters) with an order column

 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 query text, from this point I can pack it into a stored procedure, another view or into a variable for use as dynamic sql.

 SELECT txt FROM @qw 

is returning

 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:

_{(source: damirsystems.com )}

UPDATE : fixed the error in step 4, missing max () and added sample results.