将两张桌子合二为一 [英] Combine two tables in one

问题描述

我在 MSSQL Server 2012 中有两个表，想将它们合并为一个新表.它们由列 messageID 链接.第一个表(Message)有

I have two tables in MSSQL Server 2012 and would like to combine them into one new table. They are linked by the column messageID. The first table (Message) has

messageID
sender
date

第二个表(recipientInfo)有

messageID
recipient

主要问题是在Message 中，messageID 是主键，因此是唯一的.在recipientInfo 中，messageID 不是唯一的(因为一条消息可以有多个收件人).我想要一张新桌子

The main problem is that in Message the messageID is the primary key and thus unique. In recipientInfo the messageID is not unique (because one message can have multiple recipients). I'd like to have a new table with either

• 每条消息一行，收件人连接在一列中
• 每条消息多行，当有多个收件人时(每行一个收件人)

推荐答案

也许是这样的(如果您使用的是 MSSQL 2005+):

Maybe something like this (if you are using MSSQL 2005+):

CREATE TABLE NewTable
(
    messageID INT,
    sender VARCHAR(100),
    recipient VARCHAR(MAX),
    date DATETIME
)
INSERT INTO NewTable(messageID,sender,recipient,date)
SELECT
    [Message].messageID,
    [Message].sender,
    STUFF
    (
        (
            SELECT 
                ',' +recipient
            FROM
                recipientInfo
            WHERE
                recipientInfo.messageID=[Message].messageID
            FOR XML PATH('')
        )
    ,1,1,'') AS recipient,
    [Message].date
FROM
    [Message]

这篇关于将两张桌子合二为一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！