在这种情况下，我加入了两个表，由于每个医生都可以拥有多个许可证，因此它会创建多个重复项。我想将多个许可证状态和编号附加到表 1 中，如下所示。
这将需要动态完成，因为它可能是分配给一名医生的大量许可证。
表 1 看起来像这样。
assignid physician_name profession
-------------------------------
1 bob md
2 travis do
3 ryan md
4 pete pa
5 susan np
6 ashley cnp
表 2
assignid license_state license_num
-------------------------------
1 oh 561
2 mi 987
3 ca 785
4 ny 965
4 mi 125
5 oh 369
5 ca 541
加入
assignid physician_name profession license_state license_num
----------------------------------------------------------------
1 bob md oh 561
2 travis do mi 987
3 ryan md ca 785
4 pete pa ny 965
4 pete pa mi 125
5 susan np oh 369
5 susan np ca 541
我想动态地将连接表更改为如下所示。
assignid physician_name profession license_state1 license_num1 license_state2 license_num2
--------------------------------------------------------------------------------------------------
1 bob md oh 561
2 travis do mi 987
3 ryan md ca 785
4 pete pa ny 965 mi 125
5 susan np oh 369 ca 541
我尝试了这条路线，但这给了我列标题的状态。
WITH pivotdata AS (
SELECT assignid,physician_name, profession, license_state,license_num
FROM dbo.Physicians p JOIN dbo.Licenses l ON p.AssignID = l.AssignID
)
SELECT *
FROM
pivotdata
PIVOT (MAX(license_num) FOR license_state IN ([oh], [mi], [ca],[ny])) TT;
结果
physician_name profession oh mi ca ny
-------------------------------------------
bob md 561
travis do 987
ryan md 785
pete pa 125 965
susan np 369 541
您可以使用动态交叉表解决此问题。我从 Jeff Moden 和他的文章中学到了这项技术。http://www.sqlservercentral.com/articles/Crosstab/65048/
if OBJECT_ID('tempdb..#Physicians') is not null
drop table #Physicians
create table #Physicians
(
AssignID int
, PhysicianName varchar(20)
, Profession varchar(10)
)
insert #Physicians values
(1, 'bob', 'md')
, (2, 'travis', 'do')
, (3, 'ryan', 'md')
, (4, 'pete', 'pa')
, (5, 'susan', 'np')
, (6, 'ashley', 'cnp')
if OBJECT_ID('tempdb..#Licenses') is not null
drop table #Licenses
create table #Licenses
(
AssignID int
, LicenseState char(2)
, LicenseNum int
)
insert #Licenses values
(1, 'oh', 561)
, (2, 'mi', 987)
, (3, 'ca', 785)
, (4, 'ny', 965)
, (4, 'mi', 125)
, (5, 'oh', 369)
, (5, 'ca', 541)
declare @StaticPortion nvarchar(2000) =
'with OrderedResults as
(
SELECT p.AssignID
, p.PhysicianName
, p.Profession
, l.LicenseState
, l.LicenseNum
, ROW_NUMBER() over(partition by p.AssignID order by l.LicenseState) as RowNum
FROM #Physicians p
JOIN #Licenses l ON p.AssignID = l.AssignID
)
select AssignID';
declare @DynamicPortion nvarchar(max) = '';
declare @FinalStaticPortion nvarchar(2000) = ' from OrderedResults Group by AssignID order by AssignID';
--the following cte is a tally table (another trick I learned from Jeff Moden)
with E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)
select @DynamicPortion = @DynamicPortion +
', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then LicenseState end) as LicenseState' + CAST(N as varchar(6)) + CHAR(10)
+ ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then LicenseNum end) as LicenseNum' + CAST(N as varchar(6)) + CHAR(10)
from cteTally t
where t.N <=
(
select top 1 Count(p.AssignID)
FROM #Physicians p
JOIN #Licenses l ON p.AssignID = l.AssignID
group by p.AssignID
order by COUNT(*) desc
)
declare @SqlToExecute nvarchar(max) = @StaticPortion + @DynamicPortion + @FinalStaticPortion;
--you can comment the following. it is here for now so you can view the dynamic sql before it executes
select @SqlToExecute
--Once you are satisfied that the dynamic sql generated is correct simply uncomment the below line
--exec sp_executesql @SqlToExecute
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句