Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL create table using stored procedure

Posted on 2012-03-22
2
Medium Priority
?
420 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

636 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