Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to merge multiple lines of records in SQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

1. Create tables and add test data

CREATE TABLE tb (id int, [value] varchar (10)) INSERT tb SELECT 1, 'aa' UNION ALL SELECT 1,' bb' UNION ALL SELECT 2, 'aaa' UNION ALL SELECT 2,' bbb' UNION ALL SELECT 2, 'ccc'-- SELECT * FROM tb / * / * id value-- 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 row (s) affected) * /-

2 in SQL2000, you can only use custom functions

2.1Create the merge function fn_strSum, merge the value value GO CREATE FUNCTION dbo.fn_strSum (@ id int) RETURNS varchar (8000) AS BEGIN DECLARE @ values varchar (8000) SET @ values =''SELECT @ values = @ values +','+ value FROM tb WHERE id=@id RETURN STUFF (@ values, 1,1,'') END GO-- call the function SELECT id, VALUE = dbo.fn_strSum (id) FROM tb GROUP BY id DROP FUNCTION dbo.fn_strSum

2.2 create the merge function fn_strSum2, merge the value value GO CREATE FUNCTION dbo.fn_strSum2 (@ id int) RETURNS varchar (8000) AS BEGIN DECLARE @ values varchar (8000) SELECT @ values = isnull (@ values +',',') + value FROM tb WHERE id=@id RETURN @ values END GO-- call the function SELECT id, VALUE = dbo.fn_strSum2 (id) FROM tb GROUP BY id DROP FUNCTION dbo.fn_strSum2-- a new solution in SQL2005/SQL2008

3.1 use OUTER APPLY SELECT * FROM (SELECT DISTINCT id FROM tb) An OUTER APPLY (SELECT [values] = STUFF (REPLACE (REPLACE (SELECT value FROM tb N WHERE id = A.id FOR XML AUTO),','), 1, 1,')) N

3.2 use XML SELECT id, [values] = STUFF ((SELECT','+ [value] FROM tb t WHERE id=tb.id FOR XML PATH ('')), 1,1,'') FROM tb GROUP BY id-- 4 Delete the test table tb drop table tb / * * / / * id values-- 1 aa,bb 2 aaa,bbb,ccc (2 row (s) affected) * /

After reading the above, do you have any further understanding of how to merge multiple lines of records in SQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.