Join table rows as column values sql

debugcn Published at Dev

smiller48

In this situation I have two tables that I have joined, and it creates multiple duplicates due to each physician being able to have multiple licenses. I would like to pivot and make the multiple license state and numbers additional columns to table 1 as shown below.

This will need to be dynamically done since it can be an substantial amount of licenses assigned to one physician.

Table 1 looks like this.

assignid  physician_name  profession 
-------------------------------
    1        bob          md        
    2        travis       do        
    3        ryan         md        
    4        pete         pa        
    5        susan        np         
    6        ashley       cnp

Table 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

Joined

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

I want to dynamically change the joined table to look like this.

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

I attempted this route, but this gives me states as column header.

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;

Results

physician_name  profession   oh  mi  ca  ny
-------------------------------------------
    bob          md          561
    travis       do             987
    ryan         md                  785
    pete         pa             125     965
    susan        np          369     541

Sean Lange

You can solve this using a dynamic cross tab. I learned this technique from Jeff Moden and his article here. 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

Collected from the Internet

Please contact [email protected] to delete if infringement.