Menu
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.
820
To perform multi aggregate pivot we need to introduce a PIVOT operator per aggregation. The IN clause of the PIVOT operator accepts only a hard-coded, comma-separated list of spreading element values. In the situations when the values are not known, we use dynamic sql to construct the query. Thanks for reading.
Relational databases are not the only data source that lets you work with multiple tables in a PivotTable Field List. You can use tables in your workbook or import data feeds that you then integrate with other tables of data in your workbook. For more information, see Import multiple tables from other data sources.
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:
(source: damirsystems.com)
UPDATE: fixed bug at step 4, was missing max() and added results example.
81
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
