Ну, часть проблемы, которую я вижу, заключается в том, что у вас есть две таблицы, которые не нормализованы, то есть у вас есть две таблицы, которые разработаны как электронные таблицы, а не таблицы. Лучшим решением этой проблемы будет реструктуризация ваших таблиц.
Если возможно, я бы посоветовал изменить структуру таблицы на следующую:
CREATE TABLE reftblDSAColumnLabels
(
[PersonCategoryId] int,
[AntigenNum] int,
[AntigenValue] varchar(4)
);
CREATE TABLE tblDSAData
(
[PersonCategoryId] int,
[SampleDate] datetime,
[AntigenNum] int,
[AntigenValue] int
);
Таким образом, вы можете присоединиться к таблицам как на personCategoryId, так и на AntigenNum (1, 2, 3 и т. д.). Вы поймете, почему я предлагаю это через минуту.
Поскольку ваши таблицы денормализованы, будет очень сложно сгенерировать результирующий набор на лету, передав personCategoryId. Вам нужно будет использовать динамический SQL для генерации результата на основе идентификатора, отправленного в процедуру.
Чтобы получить этот результат, я бы предложил применить обе функции UNPIVOT и PIVOT. UNPIVOT возьмет ваши таблицы, которые находятся в нескольких столбцах, и преобразует их в структуры, которые я предложил выше. Это значительно упростит получение результата.
ОТКЛЮЧЕНИЕ:
Вам нужно развернуть обе таблицы, запросы на разведение будут примерно такими:
select personCategoryId,
replace(replace(col, 'Antigen', ''), 'Label', '') colNum,
value l_value
from reftblDSAColumnLabels
unpivot
(
value
for col in ([Antigen1Label], [Antigen2Label], [Antigen3Label], [Antigen4Label])
) unpiv
where PersonCategoryId = 1;
См. SQL Fiddle с демонстрацией.
select personCategoryId,SampleDate,
replace(replace(col, 'Antigen', ''), 'Value', '') colNum,
value d_value
from tblDSAData
unpivot
(
value
for col in ([Antigen1Value], [Antigen2Value], [Antigen3 Value], [Antigen4Value])
) unpiv;
См. SQL Fiddle с демонстрацией. Если вы запустите эти запросы, вы заметите, что вы получите результат, подобный этому:
| PERSONCATEGORYID | COLNUM | L_VALUE |
---------------------------------------
| 1 | 1 | A1 |
| 1 | 2 | Cw6 |
| 1 | 3 | DR15 |
| 1 | 4 | DR51 |
а также
| PERSONCATEGORYID | SAMPLEDATE | COLNUM | D_VALUE |
-------------------------------------------------------------------------
| 1 | February, 08 2013 00:00:00+0000 | 1 | 1278 |
| 1 | February, 08 2013 00:00:00+0000 | 2 | 11272 |
| 1 | February, 08 2013 00:00:00+0000 | 3 | 6880 |
| 1 | February, 08 2013 00:00:00+0000 | 4 | 7544 |
| 1 | February, 11 2013 00:00:00+0000 | 1 | 1711 |
| 1 | February, 11 2013 00:00:00+0000 | 2 | 9681 |
| 1 | February, 11 2013 00:00:00+0000 | 3 | 8437 |
| 1 | February, 11 2013 00:00:00+0000 | 4 | 8967 |
ОСНОВНОЙ
Как только эти данные будут представлены в этом формате с несколькими строками, вы можете легко объединить результаты на personCategoryId и colNum и применить функцию PIVOT, чтобы получить окончательный результат. Код с соединением и PIVOT будет таким:
select *
from
(
select l.personCategoryId, l_value, d_value, SampleDate
from
(
select personCategoryId,
replace(replace(col, 'Antigen', ''), 'Label', '') colNum,
value l_value
from reftblDSAColumnLabels
unpivot
(
value
for col in ([Antigen1Label], [Antigen2Label], [Antigen3Label], [Antigen4Label])
) unpiv
where PersonCategoryId = 1
) l
inner join
(
select personCategoryId,SampleDate,
replace(replace(col, 'Antigen', ''), 'Value', '') colNum,
value d_value
from tblDSAData
unpivot
(
value
for col in ([Antigen1Value], [Antigen2Value], [Antigen3Value], [Antigen4Value])
) unpiv
) d
on l.PersonCategoryId = d.PersonCategoryId
and l.colNum = d.colNum
) src
pivot
(
max(d_value)
for l_value in (A1, Cw6, DR15, DR51)
) piv;
См. SQL Fiddle с демонстрацией.
Теперь для вашей текущей проблемы вам нужно передать personCategoryId, чтобы заголовки столбцов менялись для каждого идентификатора. Поскольку заголовки столбцов будут меняться, вам нужно будет использовать динамический SQL, чтобы получить результат. Вы можете легко преобразовать приведенный выше код в динамический SQL, и скрипт будет выглядеть так:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@personCategoryId int = 1
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(value)
from
(
select value
from reftblDSAColumnLabels
unpivot
(
value
for col in ([Antigen1Label], [Antigen2Label], [Antigen3Label], [Antigen4Label])
) unpiv
where PersonCategoryId = @personCategoryId
) d
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT personCategoryId, SampleDate,' + @cols + '
from
(
select l.personCategoryId, l_value, d_value, SampleDate
from
(
select personCategoryId,
replace(replace(col, ''Antigen'', ''''), ''Label'', '''') colNum,
value l_value
from reftblDSAColumnLabels
unpivot
(
value
for col in ([Antigen1Label], [Antigen2Label], [Antigen3Label], [Antigen4Label])
) unpiv
where PersonCategoryId = '+cast(@personCategoryId as varchar(10))+'
) l
inner join
(
select personCategoryId,SampleDate,
replace(replace(col, ''Antigen'', ''''), ''Value'', '''') colNum,
value d_value
from tblDSAData
unpivot
(
value
for col in ([Antigen1Value], [Antigen2Value], [Antigen3Value], [Antigen4Value])
) unpiv
) d
on l.PersonCategoryId = d.PersonCategoryId
and l.colNum = d.colNum
) src
pivot
(
max(d_value)
for l_value in (' + @cols + ')
) p '
execute(@query)
См. SQL Fiddle с демонстрацией. Все версии дадут результат:
| PERSONCATEGORYID | SAMPLEDATE | A1 | CW6 | DR15 | DR51 |
---------------------------------------------------------------
| 1 | 2013-02-08 | 1278 | 11272 | 6880 | 7544 |
| 1 | 2013-02-11 | 1711 | 9681 | 8437 | 8967 |
| 1 | 2013-02-13 | 2107 | 11516 | 8958 | 7884 |
| 1 | 2013-02-15 | 1947 | 13857 | 10352 | 8719 |
| 1 | 2013-02-18 | 1917 | 10026 | 9848 | 8493 |
Изменить № 1. Если бы вы нормализовали две таблицы, вам все равно пришлось бы использовать динамический SQL для получения заголовков столбцов для каждого personCategoryId, однако вы могли бы удалить unpivot из обеих таблиц. Код будет:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@personCategoryId int = 1
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(AntigenValue)
from reftblDSAColumnLabels
where PersonCategoryId = @personCategoryId
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT personCategoryId, SampleDate,' + @cols + '
from
(
select l.personCategoryId, d.SampleDate,
l.AntigenValue l_value, d.AntigenValue d_value
from reftblDSAColumnLabels l
inner join tblDSAData d
on l.PersonCategoryId = d.PersonCategoryId
and l.AntigenNum = d.AntigenNum
) src
pivot
(
max(d_value)
for l_value in (' + @cols + ')
) p '
execute(@query)
См. SQL Fiddle с демонстрацией.
person
Taryn
schedule
29.05.2013
personCategoryIdи столбцы меняются? - person Taryn schedule 29.05.2013