SQL create table using stored procedure

I am wondering if it is possible to run a stored procedure that will create a table based on the following example.  I want to create the table below called LinkTable (either scenario) from CompanyTable.  If this is possible would it matter if CompanyTable is an sql view instead of a table?

CompanyTable
CompanyID      ContactID
100            1
100            2
100            3
200            4
200            5
300            6
300            7
300            8

LinkTable
CompanyID      ContactID
100            1,2,3
200            4,5
300            6,7,8

OR

LinkTable
CompanyID      ID1      ID2      ID3
100            1      2      3
200            4      5
300            6      7      8
schwientekdIT DirectorAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
SELECT CompanyID, LEFT(ContactID, LEN(ContactID) - 1) AS ContactID
FROM (
SELECT
    CompanyID, CAST(
    (SELECT TOP 100 PERCENT CAST(ct2.ContactID AS varchar(10)) + ','
     FROM dbo.CompanyTable ct2
     WHERE ct2.CompanyID = ct1.CompanyID
     ORDER BY ct2.ContactID
     FOR XML PATH('')
    )
    AS varchar(max)) AS ContactID
FROM (
    SELECT DISTINCT CompanyID
    FROM dbo.CompanyTable
) AS ct1
) AS derived
0
 
Scott PletcherSenior DBACommented:
Once you confirm that's working the way you want it, just add this in front of the entire SELECT above:

INSERT INTO LinkTable ( CompanyID, ContactID )
SELECT ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.