SQL SERVER 2008 字段值合并

时间:2023-03-09 06:07:13
SQL SERVER 2008 字段值合并
/**
* 通过 FOR XML PATH 语句,可以将字段的值进行合并。
**/ CREATE TABLE tb_child
(
name VARCHAR(32),
hobby VARCHAR(32)
) go INSERT INTO tb_child
VALUES ('tim',
'爬山') INSERT INTO tb_child
VALUES ('tim',
'游泳') INSERT INTO tb_child
VALUES ('tim',
'自行存') INSERT INTO tb_child
VALUES ('joe',
'自行存') INSERT INTO tb_child
VALUES ('joe',
'弹吉他') go SELECT name,
(SELECT hobby + '^'
FROM tb_child t1
WHERE t1.name = t2.name
FOR XML path(''))
FROM tb_child t2
GROUP BY name

得到的结果如下

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAATkAAABmCAIAAAA/LHEwAAAKk0lEQVR4nO2dv2vruhvG9ddcztJywFMzdbhw50I7d7loPwdK+W75D9pC6cFbhlI4UO6QA5kzlsZDh0CHkqlb6NBs3fQd5LiufrySfyiWk+fhobiyLMm2Pn4lO3HYcDicPz0IIeZPD4oZY0rKcDg8PDy05YdhOJwZwapusArDXRmswnA/DFZhuB/OWR16S7Lqnx+CoFbEhsPhIQRB0Yt1fK2AIMhP6lMZGIbjNFiF4X4YrMJwPwxWYbgfzlkVvVLnRw2GN2827+EHGw4PDztvAwxv2Kx3oM6fHobDYdeh3UudHyh4m9xXVnsRWnvRSLgvpljlaWZ0wlVvuNFGVqfT6XQ6HX/VaHTb4cGV8b/DBsDbZAeraSZyp4JbTLOqfAlW/05sDRtZTdNMGYIul0ueph0e3L7Ef7gXrhZX9Yia8CxJGrHK7LKVSbO6Wq2yN5G9ibvHd87BKrwlplgtR6rZbFYsT6dTufDx8ZGmWZJwa+lr3ggUy0zalhUTrD4/P2dZNpvN7h7f0zRryCrRBp8BAliFW7QvqwWfQojxeCxBXa1WaZodHByYi3bRWKS0FVfTLBuPx2majUa30pVY1RtsXPBvLViFWzTF6mh0+/z8PJvNpl8leZB3bmhW9U5sZNW5rNhzvnr3+J4H2CxLs2yxWLgPx1c4y3/pVoFVOLQdrBrjappmq9VKTgtHo1sbq3kFlohUztBWXH19fV0ul8vlMsvE3eP73eN7uc1Jwn1wNTa+XmvBKtyiK4+BPz4+xuNxcf+GZpXgrUl+G6uLxeJL8M8yialIeZpms9mMwNV5+aBbCFbh0KZYvbm5ybJMdv1iKij/FnPCNE2dY+BiJGns+rSMJXuOgXNWhRBCLBaLhPMk4Z53wohViKtwJ3awWh5DyptJq9VqNLrN3oQcZ97c3BC9/0tNrjtMc9P80GiCVRnwl8tl9rZmNeWCcyEET5M0zRLubi3iKhyhfVmV495ijlpMCJ2sluNqOVHJY1swmmZVgpq9iXKkXa1Wi8UizagnTLbmzRFX4QjsYLW461se98rbvzdr2Xq/0oNtYcrGcL0xcAFqlq1Z5Vzw5PX1laeJk1VmGq7bWlIv/sNwPTtYPTg4SBKu/C0vSHnVVPfJh243q5ngWVLcx14ul8/Pz9Pp1P+TG/Qqz4EAWIVb9PZ8z0be6ZWfiJDOWeWJ4MlsNkt4QrDqM52ea6wSibZ2wnA9bxWrCeenp6cJ59KS1fyzEOsxfI35qm3KrYyQwSoc1NvDqm5Jpu7Y2gnDPt5mVjt3X9oJ98I9ZlVA0C6pl6zC8A76C6tBf40DgqAmUlmt/SNWEAQFlYHVcAPuoIVHXjsEGeXfLc2shhhtly8Emx/rd1s7DBtdKTqCVRjuzGC1m9qJTybCsNFgtWrtFyfs6JfH993oL994s5pXt/mDAMdmsFqt9snZ3sm1mp8IiTZEjZ8iNrN9fcSOLzZ/EODYDFYr1W6Ocg1ZdX3f9f58H6EV7herf3583/9xfpyHHRnffh1/BqKTa5nn6GSfMca+n12c5wv3+eZ5xr3zP9Vrf7KGOJpV49drmMf35gr/OmZ6MId3zX1jdY2o5HZS3kqm/PnxXaJYXtj/MXm6P99fI6pv61P708PkbE9iT0xWlSlrsa2NVT2DvqqoF95l943VgrFi+TNaMiZZLdLLC+VsrtDqZLVsY2DU031YtcVnsArP+8/qxQn7Gi0pVn1nfa2wasxgC7l0fAar8HwrWF0TeH1ExdWn+/P90viZ5NZ/vkrESTqbMuI1bl4kYr4Kz/vP6sPkbO8zDFGsPsyfLk48BsDW2vMSPiHXp6P6sv5v9Ty4Dww/zHvGahcHRVlVPF8lYCO4taVTY2A8X4WfHuZgtXrtFyfsyBYGlXkmnU1JNObE55bgwmA1utph2OgWWG3hS+wmFc0KVH7MtUOQUY1YDSohROgqoq0dgoyqz2rouK8/nNzkXxiOzY1Y9dy4qmThv8ePXZkxFmjXIKieWmC19YtHUThjrCtWw12GIKieomYVcRWCCkXNavxxlUbaE3gy28vlgLHB5YtrQ1sh/i005fStvepav2zx7nsnCsRqo4f4fYmrnl1B+TRSpXJeLgd8IsSEyz6jFyKXiRKa9Fel9hrFbuW+d6XWWb2X3/BmbbBqiKvpz7++/bzqNK4yu8p5HEfTJSHEhDPGJzJ/ebnoo8atfEpWGlnu9HTtu7zvnQtxtXJcta1STj+R06c05yae1fnsiNJTse9xKmpWGRFX059/5dfKo/8Vq/KUvdM0bFz1SacjgLKJLYNeBR1DKrVWr53ehC5N/3fL9r1zRc2qgaKc1f9Ov7G/h4+/x49X/+6xf65+j/87/bZGtI1xsk+noTuiXkK9FNtavXvZer/ewqLHl5vq0193ed87V9SsMltcLdNYpHw5L01Da5O4qpx+24Z6n3Z2FB0JGzA+sjWYZrVSUbYN+7jvnStqVq1x1czqejDc9XzVlk2PDOWru0/PM641VmSTcUO9McbajXUZ07dy3ztX1Kwy63zVOAbOU363wS0dVyv1Bv8Uz47i7OVViSr3V2fVO7vvnStqVu3z1dKdpM+p6dXf+Vlr4d4ScfKq9gb/lEr9tVJF9Ib+zdjlfe9cgVht5Pifr/qkd9tfK0W/LWM10L53rqhZVREaHjHG2D9XoUF1xtVKvaG8iV6UksdxDlyF2FKM6cYW0s3Y5X3vXFGzyqL/PDAEbUwtsBrodRXm+eqmHPP1FdpNNWW1wfsoHEJchaCymrIa2gyvcYHhtRuxWu8K4VTQwn3Ube0QpKsFVlu/eIS+EPgIrEKxCayaBVah2ARWzfKp3fNxnD2P+S0hyqO/Wg2I9N0rOGIWeb0vpn1Wfx2vHyrX/dGkmFnVH5o7n6TbTpL+jhJ9E+IJvtIkomSfxuj11tsp437hiNHleL4vpmVWJ2d7a0RLP3naIqsvlwO5Q8VCGBGsOv8aN1Hk84YUYenodMtje/cKjljVI2YrLeAYuPYPcnuxGlgNWbWdUefZtRWi59G7jk+ZzroqtdC/ahwxn0pphWM1RFx9uRwwxhgbXL5IaF8uBwPOBzJtcpkvtMByDKyKUg8jMjfpK0p6sRfGCKBsYstgrAJHTM/mbIOiUKxOzvZY8TPk4cbAL5cDyWZ5oQ1YK7Gqn4AaPU8/o8ZVSgZbpXrhNil5bK2qlKKvxRGjU3wUhNUmoFZmVZm+dsGqLRu97FO4vomxF3qWTKTbCtFrt7WHrhpHzFmyU+2z2hDUXrPaJEroVdgu57aWOMt0ptM9T6nR2UJbO3HE6CNmU9usXh81eYs3WNWLNW7ivLrr+W0yFuuf4tnpccSIDJ5qmdXPh6uMMcbavw8sJpwxxvgkGlbF1/NazkYv+xduy0NkM5bsTN8MqwJHrJaCzFcbOvLPQgiP8+15VdZLpqtQVhHLtsLp9NCs0i3BEaMFVs3yZFVonYwu1r9nKwu2zJ4n3pME434Ru+C/yzhiVRugC6ya1W3tEKSrBVbDvcMl4H67BFah2NSU1QYvaXEIrEJQWU1ZhWF4M/an2sBqjWsDBEGhBVYhqB8CqxDUD4FVCOqHwCoE9UNgFYL6IbAKQf0QWIWgfgisQlA/9H/EIGFpUUH2CgAAAABJRU5ErkJggg==" alt="" />