Table Column Dependent on Other Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table Column Dependent on Other Tables

Author

Topic

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-07-28 : 18:20:53

I have three tables.

Group table
**************************************************************
* Group_ID * Group_Name* Clauses * Documents
**************************************************************
*....1......* Engineer.*...5.1,5.2,5.3...n*..MTL01,MTL02,etc.*
*....2......* IT.......*...1.3,4.2,5.3...n*..MIS01,MIS02,etc.*
*....3......* Finance. *...5.5,5.1,5.3...n*..EDF02,DFD05,etc.*
**************************************************************

DocToGroup junction table
************************
* Group_ID * Doc Title*
************************
*....1......*...MTL01..*
*....1......*...MTL02..*
*....2......*...MIS01..*
************************

ClauseToGroup junction table
************************
* Group_ID * *Clause*
************************
*....1......*...5.1..*
*....1......*...5.2..*
*....2......*...4.3..*
************************

How do I make the Group.Clauses, Group.Documents to automatically concatenate the rows from the junction tables and store it into the fields?

I found a code to concatenate the rows to string but I have no idea what to do next.



CREATE TABLE #RowsToString (Group_Name varchar(100), RefDoc_Title VARCHAR(100))
INSERT INTO #RowsToString
select * from dbo.jntbl_RefDocToGroup
SELECT * FROM #RowsToString

SELECT DISTINCT Group_Name, (SELECT SUBSTRING((SELECT ', ' + RefDoc_Title
FROM #RowsToString
WHERE Group_Name  = t.Group_Name
FOR XML PATH('')),2,200000)) AS RefDoc
INTO #RefDocCSV
FROM #RowsToString t
SELECT * FROM #RefDocCSV

drop table #RefDocCSV
DROP TABLE #RowsToString

Thanks All!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 02:51:32

[code]UPDATE t
SET t.Clauses = STUFF((SELECT ','+ CAST(Clause AS varchar(10)) FROM ClauseToGroup WHERE Group_ID=t.Group_ID FOR XML PATH('')),1,1,''),
t.Documents = STUFF((SELECT ','+ [Doc Title] FROM DocToGroup WHERE Group_ID=t.Group_ID FOR XML PATH('')),1,1,'')
FROM Group t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources