Hi,
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.