Синтаксис SQL для Поворота нескольких таблиц
Я потратил несколько прошлых дней, работая над этим, и распространяюсь вокруг в кругах.
Мой вопрос базируется вокруг ответа, который я принял в этом сообщении: вопрос о stackoverflow
У меня теперь есть свои данные, отошел от единственного стола на 400 столбцов к намного более managable структуре базы данных с большое спасибо 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,
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. Строка имени как столбцы, содержащие JobMeasurement. Значение или JobTrait. Значение как его данные для каждого JobFiles. Имя.
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 ответ
Я выложу несколько примеров из этой модели - потому что они у меня уже есть. Обе модели очень похожи, поэтому у вас не должно возникнуть особых проблем с освоением этой техники.
Когда дело доходит до головной боли, я считаю, что самый простой способ - идти шаг за шагом и оптимизировать позже.
Шаг 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 .
Создайте представление для создания только [Имя настройки], [Имя типа свойства], [Значение] ; обратите внимание, что в этом случае значение измерения и характеристика находятся в одном столбце. Вы, вероятно, использовали бы 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 .
Создайте список свойств (Parameters) со столбцом для сортировки к
DECLARE @Props TABLE (
id int IDENTITY (1,1)
,PropName varchar(50)
);
INSERT INTO @Props (PropName)
SELECT DISTINCT [Name]
FROM dbo.PropertyType
Step 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]
И если я запустил это:
(источник: damirsystems.com )
ОБНОВЛЕНИЕ : исправлена ошибка на шаге 4, отсутствовал max ( ) и добавил пример результатов.