SQL statement of one of the IT Ninja Turtles-connecting all values of the same field, Ninja Turtles SQL

SQL statement of one of the IT Ninja Turtles-connecting all values of the same field, Ninja Turtles SQL

Oracle can use SYS_CONNECT_BY_PATH string to aggregate functions: select ltrim (MAX (SYS_CONNECT_BY_PATH (productname, ','), ',') AS productnameFROM (SELECT '1' as id, productname, ROW_NUMBER () OVER (partition by '1' order by productname) AS curr, ROW_NUMBER () OVER (partition by '1' order by productname) -1 AS prev FROM products -- where condition order by productname) start with curr = 1 connect by prev = PRIOR curr AND id = PRIOR idGROUP BY id, but ms SQL 2000 does not have this function, however, it can be implemented through user-defined functions or procedures. -- drop function ConnectStringCreate FUNCTION ConnectString (@ name varchar (20) RETURNS varchar (1024) as begin declare @ Str varchar (1024) set @ Str = ''select @ Str = @ Str + (case when @ Str = ''then'' 'else', 'end) + [productname] from products -- where [productname] = @ name return @ Str end go -- call select DISTINCT dbo. connectString (productname) from productsSQL Server 2005 available outer apply: the following is an excerpt from the Internet: -- Sample Data DECLARE @ t TABLE (id int, value varchar (10 )) INSERT @ t SELECT 1, 'A' union all select 1, 'bb' union all select 2, 'aaa' union all select 2, 'bbb' union all select 2, 'ccc '-- Query Processing SELECT * FROM (select distinct id FROM @ t) aouter apply (SELECT [values] = STUFF (REPLACE (SELECT value FROM @ t n where id =. id for xml auto), '<N value = "', ','), '"/>', ''), 1, 1 ,'')) n/* -- result id values ----------- -------------- 1 aa, bb2 aaa, bbb, ccc (two rows affected )--*/

2 Admin/adminY/Liu Chaonan/Zhang weiru/Shan huihui/
3 Wang Yiping/Yan Lin/Yang Yu/asset manager of the Operations Management Department/Liu Ting/Zhong Yuan/du jiating/
4 branch business department Computer Administrator A Jiao/branch business department Computer Administrator B jiao/Zhang Tingting/anti-money laundering 301/credit rating 301/Chen guang/Jiang Jun/quiet/personal loan receipt 301/Shan Liwei/Sun Qing /branch sales department asset manager/Founder/Li Chen/Business Department Planning contact/Li Jing/
5 Li Juan/Shenhua sub-branch Computer Administrator A Jiao/Shenhua sub-branch Computer Administrator B jiao/Wang guoping/anti-money laundering 302/credit comprehensive 302/Zhang zhe/Zhou xiaozhou/individual loan receipt 302/Wang Zhaoyuan/Shenhua sub-branch asset Manager/yang houwei/Shenhua planning contact/
6 Anhua Sub-branch Computer Administrator A Jiao/Anhua Sub-branch Computer Administrator B jiao/Wei Hong/anti-money laundering 303/credit rating 303/Chen Ying/Xu Yukun/Chen Xing/WAN Xia/personal loan receipt 303/Garden Road sub-branch asset manager/Qiao XI/Cheng Yao/Garden Road Planning contact/
7 corner B of the Computer Administrator of the West Third Ring Road branch/corner A of the Computer Administrator of the West Third Ring Road branch/Zhang Yongfeng/anti-money laundering 304/credit synthesis 304/Deng Li/individual loan receipt 304/Zhang jinhong/xin/Li Dongdong (304) /CuI Jia/XI Sanhuan sub-branch asset manager/Zhou Qi/Li Fei/XI Sanhuan planning contact/Zhang Xin/
8 Dong Jun Yu/Zhongguancun branch Computer Administrator B jiao/Zhongguancun branch Computer Administrator A Jiao/Li Xin/anti-money laundering 305/credit comprehensive 305/individual loan receipt 305/Xu Wenwen/Dan Rui/Zhongguancun Branch asset Manager/Jia Jinping/Zhang Ying 01/CHE Shuai/Zhongguancun planning contact/

An SQL statement: concatenates all values of the same field

What is your database? Ms SQL, or Oracle ,...? There will be a lot of difference in writing different databases

Oracle can use SYS_CONNECT_BY_PATH string to aggregate functions:
Select ltrim (MAX (SYS_CONNECT_BY_PATH (productname, ','), ',') AS productname
FROM (
SELECT '1' as id, productname,
ROW_NUMBER () OVER (partition by '1' order by productname) AS curr,
ROW_NUMBER () OVER (partition by '1' order by productname)-1 AS prev
FROM products
-- Where condition
Order by productname
)
Start with curr = 1
Connect by prev = PRIOR curr AND id = PRIOR id
Group by id

However, ms SQL 2000 does not have such a function, but it can be implemented through user-defined functions or procedures.
-- Drop function ConnectString
Create FUNCTION ConnectString (@ name varchar (20 ))
RETURNS varchar (1024)
AS
BEGIN
Declare @ Str varchar (1024)
Set @ Str =''
Select @ Str = @ Str + (case when @ Str = ''then'' 'else', 'end) + [productname] from products
-- Where [productname] = @ name
Return @ Str
END
GO

-- Call
Select DISTINCT dbo. ConnectString (productname) from products

SQL Server 2005 available OUTER APPLY:
The following is an excerpt from the Internet:
-- Sample Data
DECLARE @ t TABLE (id int, value varchar (10 ))
INSERT @ t SELECT 1, 'A'
Union all select 1, 'bb'
Union all select 2, 'aaa'
Union all select 2, 'bbb'
Union all select 2, 'ccc'

-- Query Processing
SELECT *
FROM (
SELECT DISTINCT
Id
FROM @ t
)
Outer apply (
SELECT
[Values] = STUFF (REPLACE (
(
SELECT value FROM @ t N
WHERE id = A. id
FOR XML AUTO
... The remaining full text>

Change the SQL statements, and ,,,,?

Update table name set field name = the same value where your multiple records meet the common conditions