我将尝试交叉连接两个数据透视表。
--第一个数据透视表查询获取药品名称列,并生成药品ID的行值。
--第一个数据透视表查询获取药品名称列,并生成药品ID的行值。
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
Select @cols = STUFF((SELECT ',' + QUOTENAME(drug)
from _app_drugs
group by drug, drug_id,order_number
order by order_number
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select drug_id, drug
from _app_drugs
) x
pivot
(
max(drug_id)
for drug in (' + @cols + N')
) p'
exec sp_executesql @query;
--The second pivot table simply gets the signature_labels and displays the label name in the column and displays the label_id as the row value.
DECLARE @cols AS NVARCHAR(MAX),@scols as NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
Select @cols = STUFF((SELECT ',' + QUOTENAME(signature_label)
from _app_signature_labels
WHERE _app_signature_labels.isactive=1
group by signature_label_id, signature_label,ordernumber
order by ordernumber
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select signature_label_id,signature_label
from _app_signature_labels
) x
pivot
(
max(signature_label_id)
for signature_label in (' + @cols + N')
) p'
exec sp_executesql @query;
现在我只需要知道如何将这两个数据透视表合并成一个表格...它们没有共同的字段,也不需要。
有人能帮我解决这个问题吗? 谢谢