There are three tables and column have comma separated values as shown below: In table 1 there is a single value(TableValue1) and some values needs to get it from table2(Table2Name1 - get values on basis of name in table 2). In table 3 there is a group of sets present in table 2(Table3Name1 - need to fetch values from table 2 on basis of name).
Table 1 :
| ID | Name | Values |
| ------------ | ------------ | ------------ |
| 1 | test 1 | Table1Value1,Table2Name1,Table3Name1 |
| 2 | test 2 | Table1Value2,Table2Name2,Table2Name4,Table3Name2 |
Table 2 :
Name | Values |
---|---|
Table2Name1 | A,B,C |
Table2Name2 | D,E,F |
Table2Name3 | G,H |
Table2Name4 | I,J,K |
Table 3:
Name | Values |
---|---|
Table3Name1 | Table2Name1,Table2Name3 |
Table3Name2 | Table2Name2,Table2Name3 |
Result, needs to be like below :
ID | Name | Values |
---|---|---|
1 | test 1 | table1value1,A,B,C,G,H |
2 | test 2 | Table1Value2,D,E,F,I,J,K,G,H |
select *
from ( select tbl.id,tbl.name,tbl.value,table2.value
from (select id,name,value
from table1
cross apply string_split(data,',')) as tbl
left join table2 on tbl.value = table2.name) as A
left join(select b.table3name,tb.value,table2.[values]
from(select tbl3.table3name,tbl3.value
from(select tbl3.name,tbl3.[values],value
from table3
cross apply string_split(tbl3.[values],',')) as tbl3) as tb
left join table2 on table2.name = tb.value) as B on A.value = B.name
but its not showing correct data.
2条答案
按热度按时间yr9zkbsy1#
Here's my take on the issue:
I basically just follow the rabbithole of string splits from various tables. Finally, i collect the values from table2 together with what's left over from values in table1, do a grouping to remove duplicate and finally STRING_AGG them together to some fort of CSV string.
As others said, this database design ain't good, as Codd et.al. said, one should never combine multiple values into a single column, and definitely don't combine key / value data in the same column.
toe950272#
I tried but I am not sure it is not an optimized solution. But it is the exact solution. and more readable query.
My table names:
table1 (id int, nam varchar(100), val varchar(100))
table2 (name varchar(100),val varchar(100))
table3(name varchar(100), val varchar(100))