Create table help

Hi there,

i'd like to create a table from other tables. My new table will only have two columns. Let's call the new table 'C', and it's created by a Select on 'A' and 'B'

A.control_id and B.account_id

one control can have many accounts, ok. However, I'd like to create 'C' with the control_id and a concatenation of all associated account_id's.

eg:

control_id linked_accounts
----------
1 account_id1, account_id2, account_id3

Any help would be appreciated.

Regards,Have you looked into the "INSERT INTO" statement?|||It's the concatenation that's getting me. I was doing this with a function, but as soon as the report I'm creating references the function, performance goes pear-shaped.

I was doing this...

BEGIN
DECLARE @.RowCnt int
DECLARE @.MaxRows int
DECLARE @.linkedaccounts varchar(256)
DECLARE @.global_control_id int
DECLARE @.accountstr varchar (1000)

SELECT @.RowCnt = 1
SET @.accountstr=''

DECLARE @.Import TABLE ( rownum int IDENTITY (1, 1) Primary key NOT NULL,
Control_id int,global_control_id int, Title varchar(256) )

INSERT INTO @.Import
SELECT DISTINCT dbo.NTH_RPT_CONTROLS.CONTROL_ID, dbo.NTH_RPT_CONTROLS.GLOBAL_CONTROL_ID, dbo.NTH_RPT_ACCOUNT.TITLE
FROM dbo.NTH_RPT_ACCOUNT_CONTROL INNER JOIN dbo.NTH_RPT_ACCOUNT ON dbo.NTH_RPT_ACCOUNT_CONTROL.ACCOUNT_ID=dbo.NTH_RPT _ACCOUNT.ACCOUNT_ID
AND dbo.NTH_RPT_ACCOUNT_CONTROL.LABEL_ID=dbo.NTH_RPT_A CCOUNT.LABEL_ID
INNER JOIN dbo.NTH_RPT_CONTROLS
ON dbo.NTH_RPT_ACCOUNT_CONTROL.CONTROL_ID=dbo.NTH_RPT _CONTROLS.CONTROL_ID
AND dbo.NTH_RPT_ACCOUNT_CONTROL.LABEL_ID=dbo.NTH_RPT_C ONTROLS.LABEL_ID
WHERE dbo.NTH_RPT_CONTROLS.CONTROL_DID = @.control_did

SELECT @.MaxRows=count(*) from @.Import

WHILE @.RowCnt <= @.MaxRows

BEGIN

SELECT @.linkedaccounts = title, @.global_control_id = global_control_id
FROM @.Import
WHERE rownum = @.RowCnt

SELECT @.RowCnt = @.RowCnt + 1
SET @.accountstr = @.accountstr + @.linkedaccounts +', '

END
RETURN @.accountstr
END

-----------
then i created a view...
-----------

CREATE VIEW dbo.VIEW_LINKED_ACCOUNTS
AS
SELECT control_did, dbo.f_linked_accounts(dbo.nth_rpt_controls.control _did) AS account_names
FROM dbo.nth_rpt_controls

-----------

But performance is bad. so i'd like to create a permanent table that is updated by a trigger on the underlying tables.|||Im thinking keeop the function, it's still useful - but create my new table and use a trigger to execute the function and in the function send the concatenated string to the new table.