Solved

SQL create table using stored procedure

Posted on 2012-03-22
2
417 Views
Last Modified: 2012-03-23
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
0
Comment
Question by:schwientekd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37754670
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37754675
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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question