英文:
Mapping two tables with multiple values in one table
问题
我想要动态映射两个表格，它们的数据如下，我想要的结果也已发布。请帮助我如何获得这个结果。
表格：
CREATE TABLE #A
(
NAME VARCHAR(10),
NUMBER INT,
ID VARCHAR(5)
)
INSERT INTO #A VALUES ('JOHN', 20, 'J10')
CREATE TABLE #B
(
PRODUCT VARCHAR(10),
NAME VARCHAR(20)
)
INSERT INTO #B
VALUES ('APPLE', 'JOHN, HARRY'),
('ORANGE', 'JOHN,MIKE'),
('MANGO', 'JOHN, KATTY, ALEX')
结果：
+--------+-----------+-----------+----------+
| NAME | NUMBER | ID | PRODUCT |
+--------+-----------+-----------+----------+
| JOHN | 20 | J10 | APPLE |
| JOHN | 20 | J10 | ORANGE |
| JOHN | 20 | J10 | MANGO |
| MIKE | 2 | M5 | ORANGE |
+--------+-----------+-----------+----------+
请注意，这只是数据和结果的示例，如果您需要进一步的帮助或有其他问题，请告诉我。
英文:
I want to map two tables dynamically that have data like below and what kind of result I want is also posted there. Please help me with how I can get this result.
TABLES:
CREATE TABLE #A
(
NAME VARCHAR(10),
NUMBER INT,
ID VARCHAR(5)
)
INSERT INTO #A VALUES ('JOHN', 20, 'J10')
+--------+----------+------------+
|NAME | NUMBER | ID |
+--------+----------+------------+
|JOHN | 20 | J10 |
|MIKE | 2 | M5 |
+--------+----------+------------+
CREATE TABLE #B
(
PRODUCT VARCHAR(10),
NAME VARCHAR(20)
)
INSERT INTO #B
VALUES ('APPLE', 'JOHN, HARRY'),
('ORANGE', 'JOHN,MIKE'),
('MANGO', 'JOHN, KATTY, ALEX')
+--------+------------------+
|PRODUCT |NAME |
+--------+------------------+
|APPLE |JOHN, HARRY |
|ORANGE |JOHN,MIKE |
|MANGO |JOHN, KATTY, ALEX |
+--------+------------------+
SELECT * FROM #A
SELECT * FROM #B
Result:
+--------+-----------+-----------+----------+
|NAME | NUMBER | ID | PRODUCT |
+--------+-----------+-----------+----------+
| JOHN | 20 | J10 | APPLE |
| JOHN | 20 | J10 | ORANGE |
| JOHN | 20 | J10 | MANGO |
| MIKE | 2 | M5 | ORANGE |
+--------+-----------+-----------+----------+
答案1
得分: 0
I hope that fixing the design is part of the solution here. But given the data as posted you have to jump through some ugly hurdles to overcome the denormalized design. That being said here is one way to get the desired output from the sample provided.
CREATE TABLE #A
(
NAME VARCHAR(10),
NUMBER INT,
ID VARCHAR(5)
)
INSERT INTO #A VALUES ('JOHN', 20, 'J10')
, ('MIKE', 2, 'M5')
CREATE TABLE #B
(
PRODUCT VARCHAR(10),
NAME VARCHAR(20)
)
INSERT INTO #B
VALUES ('APPLE', 'JOHN, HARRY'),
('ORANGE', 'JOHN,MIKE'),
('MANGO', 'JOHN, KATTY, ALEX')
select a.NAME
, a.NUMBER
, a.ID
, b.PRODUCT
from #A a
join
(
select x.name
, b.PRODUCT
from #B b
cross apply(select trim(value) from string_split(b.NAME, ',')) x(name)
) b on b.NAME = a.NAME
order by a.NAME
, a.NUMBER
, a.ID
, b.PRODUCT
drop table #A
drop table #B
英文:
I hope that fixing the design is part of the solution here. But given the data as posted you have to jump through some ugly hurdles to overcome the denormalized design. That being said here is one way to get the desired output from the sample provided.
CREATE TABLE #A
(
NAME VARCHAR(10),
NUMBER INT,
ID VARCHAR(5)
)
INSERT INTO #A VALUES ('JOHN', 20, 'J10')
, ('MIKE', 2, 'M5')
CREATE TABLE #B
(
PRODUCT VARCHAR(10),
NAME VARCHAR(20)
)
INSERT INTO #B
VALUES ('APPLE', 'JOHN, HARRY'),
('ORANGE', 'JOHN,MIKE'),
('MANGO', 'JOHN, KATTY, ALEX')
select a.NAME
, a.NUMBER
, a.ID
, b.PRODUCT
from #A a
join
(
select x.name
, b.PRODUCT
from #B b
cross apply(select trim(value) from string_split(b.NAME, ',')) x(name)
) b on b.NAME = a.NAME
order by a.NAME
, a.NUMBER
, a.ID
, b.PRODUCT
drop table #A
drop table #B
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论