CODEWITHSUNDEEP
sql-serverlookupcreate-tablemultiple-records
mgraham
mgraham

Reputation: 11

SQL Server - Need to create multiple rows based on lookup value

How do I create multiple rows in a new table based on a common value in another table? 

   ProviderTable:                                        PersonTable:
--------------------                                ---------------------
ProviderID | GroupID                                PersonID | ProviderID
    1      |    A                                      100   |      1
    2      |    A                                      101   |      3                                      
    3      |    A                                      102   |      8
    4      |   NULL                                    103   |      10
    5      |    B                                      104   |      5
    6      |    C                                      105   |      4
    7      |    B
    8      |   NULL
    9      |   NULL
    10     |    C

ProviderTable.ProviderID = PersonTable.ProviderID

I need to create a new table with a person row for each provider where Provider.GroupID=Provider.GroupID

Results I am looking for: 

    New-table: 
PersonID | ProviderID
   100   |      1
   100   |      2
   100   |      3
   101   |      3
   101   |      1
   101   |      2
   102   |      8
   103   |      10
   103   |      6
   104   |      5
   104   |      7
   105   |      4

Upvotes: 0

Views: 150

Answers (2)

Bill Tür stands with Ukraine
Bill Tür stands with Ukraine

Reputation: 3515

You can use the following SQL-statement:

SELECT PER.PersonID, COALESCE(PG.ProviderID, PP.ProviderID) AS ProviederID
   FROM PersonTable PER
      INNER JOIN ProviderTable PP
         ON PP.ProviderID = PER.ProviderID
      LEFT OUTER JOIN ProviderTable PG
         ON PG.GroupID = PP.GroupID;

Upvotes: 0

Laughing Vergil
Laughing Vergil

Reputation: 3756

This quick version gets the sort order you are looking for. Test data is included:

DECLARE @pt table (
    ProviderId int, 
    GroupId varchar(2)
    )

DECLARE @pet table (
    PersonId int, 
    ProviderId int
    )

INSERT INTO @pt Values 
    (1,'A'),
    (2,'A'),
    (3,'A'),
    (4,NULL),
    (5,'B'),
    (6,'C'),
    (7,'B'),
    (8,NULL),
    (9,NULL),
    (10,'C')

INSERT INTO @pet VALUES 
    (100,1),
    (101,3),
    (102,8),
    (103,10),
    (104,5),
    (105,4)

SELECT pe.PersonId, 
    IsNull(p2.ProviderId, p1.providerId) As ProviderId
FROM @pt p1
INNER JOIN @pet pe
    ON p1.ProviderId = pe.ProviderId
LEFT JOIN @pt p2
    ON p1.GroupId = p2.GroupId
ORDER BY pe.personId,
    CASE 
        WHEN pe.ProviderId = p2.ProviderId 
            Then 0
        ELSE 1
    END

Upvotes: 1

Related Questions