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.
| Author |
Topic |
|
anujpratap84
Starting Member
45 Posts |
 Posted - 2012-02-17 : 05:49:25
|
| Hi All,I am trying to create query on two table Test1 and test2.First Tbale have columns 1)ID 2)CombinationsSecond table have Columns 1)test1_ID 2) DataSecond table test1_ID is reference from table1 ID column and second column contains the valueFirst table 2nd column Combinations contains combination of IDs(Comma seperated values from ID column).Like:ID Combinations 1 2,3 2 null 3 null 4 3test1_ID Data 1 A 2 B 3 C 4 D Ex: if i pass 1 then result will be A,B,CSo my requirement is to get comma seperated values of Data column from second table based on the passed id.but if passed id have more id in combinations column then it also get the value of those id and if combination column id also have combination then get those id value also in the same result set.Means query is get comma seperated data from second table until first table combination column is not null.Can you please provide a sample query.Thanks in advance.Anuj Pratap Singh |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-17 : 07:48:41
|
| While what you are asking is certainly possible, it is by no means trivial. The query would require splitting the comma-separated strings into a virtual table and use of a recursive query by joining that virtual table while at the same time making sure that if there are circular relationships they are correctly handled without causing infinite recursions etc.If there is an opportunity to redesign the database in a normalized form, that would make querying and maintaining much simpler.I know I am not offering a solution here; would have loved to, but time constraints do not permit me. Hopefully others on the forum may have more useful suggestions. |
 |
|
|
|
|
|
Recursive query issue CTE