创建多行的串联SQL查询 [英] Create concatenate SQL query for multiple rows
问题描述
我们正在尝试在Sybase中创建一个SQL查询，在该查询中我们可以将多行连接到一个选定的行中。
We are trying to create a SQL query in Sybase where we can concatenate multiple rows into one selected row.
布局：
| Type | Skill |
----------------
| A | 1
| A | 2
| B | 1
ETC
我希望输出为：A（1,2）
I want the output to be like: A (1,2)
推荐答案
如果使用ASE 16，则可以滚动自己的函数来模拟list（）函数；在此线程中查看我的答复：在ASE中实现group_concat（）
If using ASE 16 you may be able to roll your own function to simulate the list() function; see my responses in this thread: Implementing group_concat() in ASE
请注意爱迪生有关KBA 2260479的响应...在事务中使用表变量时出现错误。
Pay attention to Edison's response about KBA 2260479 ... a bug when using table variables inside a transaction.
------ -----根据从sap.com链接复制相关信息的建议...
----------- per the suggestion to copy pertinent info from sap.com link ...
请参阅示例@ Percona示例（很抱歉，也不会复制该链接；这篇文章所附的示例显示了无论如何都匹配percona示例的结果）
Referring to an example @ Percona example (sorry, not going to replicate that link, too; the examples attached to this post show the results of matching the percona examples anyway)
要在ASE中模仿MySQL的GROUP_CONCAT（）函数，我们需要了解一些问题/局限性/注意事项：
To mimic MySQL's GROUP_CONCAT() function in ASE we need to understand some issues/limitations/observations:
-
ASE不允许聚合/ UDF（例如，将结果集作为参数传递给函数）
ASE does not allow aggregate/UDFs (eg, passing a result set as an argument to a function)
ASE不允许使用表v定义UDF ariable作为输入参数
ASE does not allow defining a UDF with a table variable as an input parameter
ASE不允许在UDF中创建#temp表
ASE does not allow the creation of a #temp table inside a UDF
ASE 16 SP02 + DOES 允许在UDF中创建/使用表变量
ASE 16 SP02+ DOES allow the creation/use of table variables inside a UDF
ASE DOES 允许UDF内的exec（）构造
ASE DOES allow the exec() construct inside a UDF
GROUP_CONCAT（）参数由要附加的列/字符串组成，即 order by子句和可选的SEPARATOR（请参见上面percona.com链接上的示例）；不难看出column / strings +'order by'子句是SELECT查询的组成部分
the GROUP_CONCAT() argument consists of columns/strings to be appended, an 'order by' clause, and an optional SEPARATOR (see examples at the percona.com link - above); it's not too hard to see that the columns/strings + 'order by' clause are components of a SELECT query
我们可以模拟将数据集传递给通过传递表示所需数据集的SQL / SELECT查询代替UDF；然后可以通过exec（）构造执行此SQL / SELECT查询，以向UDF提供所需的数据集
we can simulate passing of a data set to the UDF by instead passing a SQL/SELECT query that represents the desired dataset; this SQL/SELECT query can then be executed via the exec() construct to provide the UDF with the desired data set
我们的UDF设计要点：
The gist of our UDF design:
注意：由于我们使用表变量，因此需要ASE 16.0 SP02 +
NOTE: Due to our use of a table variable the following requires ASE 16.0 SP02+
1-UDF的输入参数-@sql varchar（XXX）-表示调用进程提供的SQL / SELECT语句
1 - the UDF's input parameter - @sql varchar(XXX) - represents a SQL/SELECT statement supplied by the calling process
1a- @sql必须是完整的独立查询（即，您应该能够在单独的ASE会话中单独运行查询）
1a- @sql must be a complete stand-alone query (ie, you should be able to run the query on its own in a separate ASE session)
1b-@sql的选择/投影列表与表变量的列匹配（请参阅下一个项目符号）
1b - the select/projection list of @sql matches the column(s) of the table variable (see next bullet)
1c-@sql包含任何必需的' group / order by'子句（即UDF将不执行任何排序操作）
1c - @sql includes any necessary 'group/order by' clauses (ie, the UDF will not perform any sort operations)
2-UDF创建一个表变量，该变量定义了一个列，用于保存结果@sql查询
2 - the UDF creates a table variable with a column defined to hold the results of the @sql query
2a-表变量的列必须匹配，明智的数据类型，使用@sql查询的选择/投影列表
2a - the column(s) of the table variable must match, datatype wise, with the select/projection list of the @sql query
3-UDF通过exec（）构造填充表变量：
3 - the UDF populates the table variable via the exec() construct:
exec("insert @table_var " + @sql)
4-UDF使用游标遍历表变量中的记录
4 - the UDF uses a cursor to loop through the records in the table variable
4a-游标没有 order by子句=>假设在插入表变量后，行基于@sql
4a - cursor does not have a 'order by' clause => assume rows are ordered, upon insert to the table variable, based on @sql
有关此特定内容的一些详细信息UDF：
Some specifics about this particular UDF:
1-我已经在sybsystemprocs数据库中创建了UDF并将其命名为：
1 - I've created the UDF in the sybsystemprocs database and named it:
sp_f_group_concat
1a-'sp_'前缀意味着可以执行UDF从任何数据库内部
1a - the 'sp_' prefix means the UDF can be executed from within any database
1b- f_字符串使我可以快速/直观地看到这是一个函数，而不是系统存储的proc
1b - the 'f_' string allows me to quickly/visually see that this is a function and not a system stored proc
2-已创建UDF，并假定传入SQ L / SELECT查询将具有一个由单个varchar（100）列组成的选择/投影列表
2 - the UDF has been created with the assumption that the incoming SQL/SELECT query will have a select/projection list consisting of a single varchar(100) column
2a-调用过程将需要执行任何必要的数据类型转换（以char）和列/字符串的任何串联
2a - the calling process will need to perform any necessary datatype casting (to char) and any concatenation of columns/strings
2b-@sql输入参数已定义为varchar（1000），@ separator已定义为varchar（ 10）默认为单个逗号（'，'）
2b - the @sql input parameter has been defined as varchar(1000), and the @separator has been defined as varchar(10) with a default of a single comma (',')
2c-UDF的所有者需要根据他们的期望修改varchar（）的长度在其环境中处理
2c - the owner of the UDF will need to revise the varchar() lengths based on what they expect to handle in their environment
3-因为UDF无法确定TF：7703（允许将数据逐行累积到单个@variable中），并且UDF不执行任何排序，我们将使用游标浏览表变量中的记录
3 - since the UDF cannot be sure TF:7703 (allow accumulating of data, by row, into a single @variable) is enabled, and the UDF does not perform any sorting, we'll use a cursor to step through the records in our table variable
4-从percona.com示例中看不出来MySQL的GROUP_CONCAT（）函数处理以非NULL值附加NULL的问题（例如，是否忽略NULL？ NULL是否转换为空字符串’？ NULL是否转换为字符串 NULL？）；最终结果是，如果UDF所有者/用户发现未按需要处理NULL，则可能需要重新访问UDF和/或@sql设计
4 - it's not apparent from the percona.com examples how MySQL's GROUP_CONCAT() function handles appending NULL's with non-NULL values (eg, is the NULL ignored? is the NULL converted to the empty string ''? is the NULL converted to the string 'NULL'?); net result is that the UDF owner/user may need to revisit the UDF and/or @sql design if they find NULL's are not being handled as desired
嗯，无法将文件附加到stackoverflow帖子？好的，所以剪切粘贴是...，不完全是源代码...
Hmmmm, can't attach files to stackoverflow posts? ok, so cut-n-paste it is ... yuck, not quite what the source looks like ...
++++++++++ ++++ sp_f_group_concat.sql
++++++++++++++ sp_f_group_concat.sql
- UDF DDL
use sybsystemprocs
go
if object_id('sp_f_group_concat') is not null
drop function sp_f_group_concat
go
create function sp_f_group_concat
(@sql varchar(1000)
,@separator varchar(10) = NULL
)
returns varchar(1000)
as
/*
sp_f_group_concat
ASE implementation of MySQL's GROUP_CONCAT() function.
See https://ideas.sap.com/D36082 for a discussion of this topic, along
with some examples (as attachments to one of Mark's comments)
Requirements/Assumptions
========================
- ASE version must support a) user defined functions and b) table variables
- @sql is a standalone query that generates a result set consisting of a single varchar column
- @sql includes an 'order by' clause if needed (ie, this function does not attempt to order the results generated by @sql)
History
=======
10/10/2016 Mark A. Parsons Initial coding
*/
set nocount on
declare @string varchar(100),
@string_list varchar(1000)
-- default delimiter to ',' if not supplied
select @separator = isnull(@separator,',')
-- create/populate @strings table
declare @strings table (string varchar(100))
exec("insert @strings " + @sql)
-- assume TF:7703 is not enabled, so can't use a single SELECT to append to a @variable
-- assume @sql has a 'order by' clause and that our cursor will pull from @strings in the same order
declare string_cur cursor
for
select string
from @strings
for read only
-- loop through @strings rows, appending individual strings to @string_list
open string_cur
fetch string_cur into @string
while @@sqlstatus = 0
begin
select @string_list = @string_list + case when @string_list is not NULL then @separator end + @string
fetch string_cur into @string
end
close string_cur
deallocate cursor string_cur
-- send concatenated list of strings back to calling process
return @string_list
go
grant execute on sp_f_group_concat to public
go
++++++++++++++ sp_f_group_concat.test1.sql
++++++++++++++ sp_f_group_concat.test1.sql
- percona.com group_c表的示例
- 第一个查询显示使用默认分隔符（单个逗号）
- 第二个查询显示使用3字符分隔符
use tempdb
go
set nocount on
go
/*
reproduction of the MySQL/GROUP_CONCAT() examples from:
https://www.percona.com/blog/2013/10/22/the-power-of-mysql-group_concat/
*/
if object_id('group_c') is not NULL
drop table group_c
go
create table group_c
(parent_id int NULL
,child_id int NULL
)
go
insert group_c values (1,1)
insert group_c values (1,1)
insert group_c values (1,2)
insert group_c values (1,3)
insert group_c values (1,4)
insert group_c values (2,1)
insert group_c values (2,4)
insert group_c values (2,6)
insert group_c values (3,1)
insert group_c values (3,2)
insert group_c values (4,1)
insert group_c values (4,1)
insert group_c values (5,0)
go
-----
print "
******************************
QUERY # 1 : List of parents and associated children (default separator = ',')
******************************
"
select parent_id,
-- we know child_id_list is relatively narrow in this case
-- so reduce the width of the output via left(--,20)
left( dbo.sp_f_group_concat("select distinct
convert(varchar(100), child_id)
from group_c
where parent_id = " + convert(varchar, parent_id) + "
order by child_id"
, default
)
,20) as child_id_list
from group_c
group by parent_id
order by parent_id
/*
results should look like:
parent_id child_id_list
----------- --------------------
1 1,2,3,4
2 1,4,6
3 1,2
4 1
5 0
*/
go
-----
print "
******************************
QUERY # 1 : List of parents and associated children (separator = ' - ')
******************************
"
select parent_id,
-- we know child_id_list is relatively narrow in this case
-- so reduce the width of the output via left(--,20)
left( dbo.sp_f_group_concat("select distinct
convert(varchar(100), child_id)
from group_c
where parent_id = " + convert(varchar, parent_id) + "
order by child_id"
, " - "
)
,20) as child_id_list
from group_c
group by parent_id
order by parent_id
/*
results should look like:
parent_id child_id_list
----------- --------------------
1 1 - 2 - 3 - 4
2 1 - 4 - 6
3 1 - 2
4 1
5 0
*/
go
++++++++++++++ sp_f_group_concat.test2.sql
++++++++++++++ sp_f_group_concat.test2.sql
- percona.com示例使用工程师/客户/问题/工作流程表
- 第二次查询（嵌套的GROUP_CONCAT（）调用）使用临时表存储中间结果，因为单查询解决方案太复杂/复杂了
use tempdb
go
set nocount on
go
/*
reproduction of the MySQL/GROUP_CONCAT() examples from:
https://www.percona.com/blog/2013/10/22/the-power-of-mysql-group_concat/
Assumptions
===========
- ASE's identity column attribute generates the same results as
MySQL's AUTO_INCREMENT column attribute
- otherwise the auto-generated customer.id values won't
match the manually entered issues.company_id values
- otherwise the auto-generated engineers.id and issues.id
values won't match the manually entered values for
workflow's engineer_id/issue_id pairs
*/
if object_id('engineers') is not NULL
drop table engineers
go
create table engineers
(id smallint identity
,e_name varchar(30) not NULL
,e_surname varchar(30) not NULL
,url varchar(255) not NULL
)
go
alter table engineers
add primary key (id)
go
insert engineers (e_name, e_surname, url) values ('Miguel', 'Nieto', 'https://www.percona.com/about-us/our-team/miguel-angel-nieto')
insert engineers (e_name, e_surname, url) values ('Marcos', 'Albe', 'https://www.percona.com/about-us/our-team/marcos-albe')
insert engineers (e_name, e_surname, url) values ('Valerii', 'Kravchuk', 'https://www.percona.com/about-us/our-team/valerii-kravchuk')
insert engineers (e_name, e_surname, url) values ('Michael', 'Rikmas', 'https://www.percona.com/about-us/our-team/michael-rikmas')
go
if object_id('customers') is not NULL
drop table customers
go
create table customers
(id smallint identity
,company_name varchar(30) not NULL
,url varchar(255) not NULL
)
go
alter table customers
add primary key (id)
go
insert customers (company_name, url) values ('OT','http://www.ovaistariq.net/')
insert customers (company_name, url) values ('PZ','http://www.peterzaitsev.com/')
insert customers (company_name, url) values ('VK','http://mysqlentomologist.blogspot.com/')
insert customers (company_name, url) values ('FD','http://www.lefred.be/')
insert customers (company_name, url) values ('AS','http://mysqlunlimited.blogspot.com/')
insert customers (company_name, url) values ('SS','https://www.flamingspork.com/blog/')
go
if object_id('issues') is not NULL
drop table issues
go
create table issues
(id smallint identity
,customer_id smallint not NULL
,description text
)
go
alter table issues
add primary key (id)
go
insert issues (customer_id, description) values (1,'Fix replication')
insert issues (customer_id, description) values (2,'Help with installation of Percona Cluster')
insert issues (customer_id, description) values (3,'Hardware suggestions')
insert issues (customer_id, description) values (4,'Error: no space left')
insert issues (customer_id, description) values (5,'Help with setup daily backup by Xtrabackup')
insert issues (customer_id, description) values (6,'Poke sales about Support agreement renewal')
insert issues (customer_id, description) values (4,'Add more accounts for customer')
insert issues (customer_id, description) values (2,'Create Hot Fix of Bug 1040735')
insert issues (customer_id, description) values (1,'Query optimisation')
insert issues (customer_id, description) values (1,'Prepare custom build for Solaris')
insert issues (customer_id, description) values (2,'explain about Percona Monitoring plugins')
insert issues (customer_id, description) values (6,'Prepare access for customer servers for future work')
insert issues (customer_id, description) values (5,'Decribe load balancing for pt-online-schema-change')
insert issues (customer_id, description) values (4,'Managing deadlocks')
insert issues (customer_id, description) values (1,'Suggestions about buffer pool size')
go
if object_id('workflow') is not NULL
drop table workflow
go
create table workflow
(action_id int identity
,engineer_id smallint not NULL
,issue_id smallint not NULL
)
go
alter table workflow
add primary key (action_id)
go
insert workflow (engineer_id, issue_id) values (1,1)
insert workflow (engineer_id, issue_id) values (4,2)
insert workflow (engineer_id, issue_id) values (2,3)
insert workflow (engineer_id, issue_id) values (1,4)
insert workflow (engineer_id, issue_id) values (3,5)
insert workflow (engineer_id, issue_id) values (2,6)
insert workflow (engineer_id, issue_id) values (3,7)
insert workflow (engineer_id, issue_id) values (2,8)
insert workflow (engineer_id, issue_id) values (2,9)
insert workflow (engineer_id, issue_id) values (1,10)
insert workflow (engineer_id, issue_id) values (3,11)
insert workflow (engineer_id, issue_id) values (2,12)
insert workflow (engineer_id, issue_id) values (2,13)
insert workflow (engineer_id, issue_id) values (3,14)
insert workflow (engineer_id, issue_id) values (1,15)
insert workflow (engineer_id, issue_id) values (1,9)
insert workflow (engineer_id, issue_id) values (4,14)
insert workflow (engineer_id, issue_id) values (2,9)
insert workflow (engineer_id, issue_id) values (1,15)
insert workflow (engineer_id, issue_id) values (3,10)
insert workflow (engineer_id, issue_id) values (4,2)
insert workflow (engineer_id, issue_id) values (2,15)
insert workflow (engineer_id, issue_id) values (4,8)
insert workflow (engineer_id, issue_id) values (4,4)
insert workflow (engineer_id, issue_id) values (3,11)
insert workflow (engineer_id, issue_id) values (1,7)
insert workflow (engineer_id, issue_id) values (3,7)
insert workflow (engineer_id, issue_id) values (1,1)
insert workflow (engineer_id, issue_id) values (1,9)
insert workflow (engineer_id, issue_id) values (3,4)
insert workflow (engineer_id, issue_id) values (4,3)
insert workflow (engineer_id, issue_id) values (1,5)
insert workflow (engineer_id, issue_id) values (1,7)
insert workflow (engineer_id, issue_id) values (1,4)
insert workflow (engineer_id, issue_id) values (2,4)
insert workflow (engineer_id, issue_id) values (2,5)
go
print "
******************************
QUERY # 1 : List of issues for each engineer
******************************
"
/*
for display purposes we'll use left() to reduce column widths based on known max widths for the test data
*/
select left(e.e_name + ' ' + e.e_surname, 20) as engineer,
left(dbo.sp_f_group_concat("select distinct
convert(varchar,w.issue_id) + ' (' + c.company_name + ')'
from workflow w,
engineers e,
customers c,
issues i
where w.engineer_id = e.id
and w.issue_id = i.id
and i.customer_id = c.id
and e.id = " + convert(varchar,e.id) + "
order by w.issue_id"
, ', ')
, 80) as 'issue (customer)'
from workflow w,
engineers e,
customers c,
issues i
where w.engineer_id = e.id
and w.issue_id = i.id
and i.customer_id = c.id
group by e.id
order by e_name, e_surname
/*
results should look like:
engineer issue (customer)
------------------------------ --------------------------------------------------------------------------------
Marcos Albe 3 (VK), 4 (FD), 5 (AS), 6 (SS), 8 (PZ), 9 (OT), 12 (SS), 13 (AS), 15 (OT)
Michael Rikmas 2 (PZ), 3 (VK), 4 (FD), 8 (PZ), 14 (FD)
Miguel Nieto 1 (OT), 4 (FD), 5 (AS), 7 (FD), 9 (OT), 10 (OT), 15 (OT)
Valerii Kravchuk 4 (FD), 5 (AS), 7 (FD), 10 (OT), 11 (PZ), 14 (FD)
*/
go
print "
******************************
QUERY # 2 : List of engineers for each customer (nested group_concat() calls)
******************************
"
/*
while technically possible to nest our sp_f_group_concat() calls, the outer
call becomes unwieldly since it will have to duplicate a copy of the inner
call (and the full text for the e_list derived table) for each company;
reason being that the e_list derived table has to be re-created for each
outer call (per company)
to make the code easier to read we're going to materialize the e_list derived table
as a #temp table; for large data sets we'd want to look at the feasibilty of
adding an index for performance reasons
for display purposes we'll use left() to reduce column widths based on known
max widths for the test data
*/
-- build/populate the #e_list table with a set of issue id's and associated engineer lists
if object_id('#e_list') is not NULL
drop table #e_list
go
create table #e_list
(i_id int
,engineer_list varchar(1000)
)
go
insert #e_list
select i.id as i_id,
dbo.sp_f_group_concat("select distinct
e.e_name + ' ' + e.e_surname
from workflow w,
engineers e,
issues i
where w.engineer_id = e.id
and w.issue_id = i.id
and i.id = " + convert(varchar, i.id) + "
order by e.e_name, e.e_surname"
, ', ') as engineer_list
from workflow w,
engineers e,
issues i
where w.engineer_id = e.id
and w.issue_id = i.id
group by i.id
go
-- now run the main query to display isuses/engineer-lists by company
select left(c.company_name, 10) as company,
left(dbo.sp_f_group_concat("select distinct
convert(varchar,e_list.i_id) + ' (' + e_list.engineer_list + ')'
from workflow w,
engineers e,
customers c,
issues i,
#e_list e_list
where w.engineer_id = e.id
and w.issue_id = i.id
and i.customer_id = c.id
and w.issue_id = e_list.i_id
and c.id = " + convert(varchar, c.id) + "
order by w.issue_id"
, ', ' )
, 140) as issue
from workflow w,
engineers e,
customers c,
issues i,
#e_list e_list
where w.engineer_id = e.id
and w.issue_id = i.id
and i.customer_id = c.id
and w.issue_id = e_list.i_id
group by c.id
order by c.company_name
/*
results should look like:
company issue
---------- --------------------------------------------------------------------------------------------------------------------------------------------
AS 5 (Marcos Albe, Miguel Nieto, Valerii Kravchuk), 13 (Marcos Albe)
FD 4 (Marcos Albe, Michael Rikmas, Miguel Nieto, Valerii Kravchuk), 7 (Miguel Nieto, Valerii Kravchuk), 14 (Michael Rikmas, Valerii Kravchuk)
OT 1 (Miguel Nieto), 9 (Marcos Albe, Miguel Nieto), 10 (Miguel Nieto, Valerii Kravchuk), 15 (Marcos Albe, Miguel Nieto)
PZ 2 (Michael Rikmas), 8 (Marcos Albe, Michael Rikmas), 11 (Valerii Kravchuk)
SS 6 (Marcos Albe), 12 (Marcos Albe)
VK 3 (Marcos Albe, Michael Rikmas)
*/
go
++++++++++++++++
++++++++++++++
注意：对于示例查询，您将请注意，传递给sp_f_group_concat（）函数的@sql字符串基本上是父查询 plus 的一个副本，其中附加了一个'where'子句，以允许将查询限制为仅与父查询的' group by子句（即，附加的 where子句与 group by子句中的列匹配）
NOTE: For the example queries you'll notice that the @sql string passed to the sp_f_group_concat() function is basically a copy of the parent query plus an additional 'where' clause to allow limiting the query to just the rows that match the parent query's 'group by' clause (ie, the additional 'where' clause matches the column(s) in the 'group by' clause)
这篇关于创建多行的串联SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
