如何根据日期和department_id将多行返回到单个行? [英] How can I return multiple rows into single rows as per date and department_id?
本文介绍了如何根据日期和department_id将多行返回到单个行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个名为Transaction的表,其中包含以下字段
create 表 TransactionTable(
Department_id int 不 null ,
日期 日期,
comments varchar ( 20 )
);
插入 进入 TransactionTable(Department_id, date ,comments) values ( 1 , ' 2/2/2018',' comment 2');
插入 进入 TransactionTable(Department_id, date ,comments)值( 1 ,' 2/2/2018',' comment 3' 跨度>);
插入 进入 TransactionTable(Department_id, date ,comments)值( 1 ,' 5/5/2017',' comment 3' 跨度>);
插入 进入 TransactionTable(Department_id, date ,comments)值( 1 ,' 4/5/2018',' comment 3' 跨度>);
插入 进入 TransactionTable(Department_id, date ,comments)值( 1 ,' 6/2/2018',' comment 5' 跨度>);
插入 进入 TransactionTable(Department_id, date ,comments)值( 3 ,' 3/4/2018',' comment 2' 跨度>);
插入 进入 TransactionTable(Department_id, date ,comments)值( 3 ,' 5/5/2017',' comment 3' 跨度>);
插入 进入 TransactionTable(Department_id, date ,comments)值( 3 ,' 5/5/2017',' comment 2' 跨度>);
插入 进入 TransactionTable(Department_id, date ,comments)值( 3 ,' 4/5/2018',' comment 3' 跨度>);
Department_ID 日期评论
1 2018-02-02评论 1
1 2018-02-02评论 2
1 2018-03-04评论 2
< span class =code-digit> 1 2017-02-04 comment 3
1 2017-05-05评论 3
1 2018-04-05评论 3
1 2018-06-02评论 5
3 2018-03-04评论 2
< span class =code-digit> 3 2017-02-04评论 3
3 2017-05-05评论 3
3 2017-05-05评论 2
3 2018-04-05评论 3
我的输出看起来像这样
Department_ID日期评论
1 2018-02-02 comment1,comment2
3 2017-05-05 comment 3,comment 2
我尝试过:
SELECT Department_DATE, DATE ,comment1 = STUFF( (
SELECT distinct ' ,' + comment
FROM TransactionTable t1 其中 t1.Department_ID = t2.Department_ID
FOR XML PATH(' )), 1 , 1 ,' ')
FROM TransactionTable t2
GROUP BY Department_ID, DATE ;
解决方案
WITH CTE AS (
SELECT DISTINCT Department_ID, DATE ,
STUFF(( SELECT distinct ' ,' + comments
FROM TransactionTable t1 其中 t1。 Department_ID = t2.Department_ID
AND CAST(t1。日期 AS DATE )= CAST(t2。日期 AS DATE )
FOR XML PATH(' ')), 1 , 1 ,' '
) AS 条评论
FROM TransactionTable t2)
选择 * 来自 CTE 其中 CHARINDEX(' ,comm',comments)<> 0 - 或者评论LIKE '%,COMM%';
SELECT DISTINCT p.Department_id,Date,
STUFF((SELECT distinct','+ p1 .comments
FROM TransactionTable p1
WHERE p.Department_id = p1.Department_id
FOR XML PATH(''),TYPE
).value('。','NVARCHAR(MAX) )')
,1,1,'')CommentValue
来自TransactionTable p;
希望这会帮你
试试这个
IF OBJECT_ID (N ' tempdb .. #ExchangeTable',N ' U') IS NOT NULL
DROP TABLE #TransactionTable;
创建 表 #TransactionTable(
Department_id int 不 null ,
date date ,
条评论 varchar ( 20 )
);
插入 进入 #TransactionTable(Department_id ,日期,评论)
值
( 1 ,' 2/2/2018',' comment 2'),
( 1 , ' 2/2/2018',' comment 3'),
( 1 ,' 5/5/2017',' 评论3'),
( 1 ,' 4/5/2018',' c omment 3'),
( 1 ,' 6/2/2018',' comment 5'),
( 3 ,' 3/4 / 2018',' comment 2'),
( 3 ,' 5/5/2017',' comment 3'),
( 3 ,' 5/5/2017',' comment 2'),
( 3 ,' 4/5/2018',' 评论3');
选择 * 来自 #TransactionTable 订单 Department_id, DATE ,评论
SELECT Department_id, DATE ,comment1 = STUFF((
SELECT distinct ' , ' + comments
FROM #TransactionTable t1 其中 t1.Department_ID = t2。 Department_ID 和 t1.date = t2。 date
FOR XML PATH(' ')), 1 , 1 ,' ')
FROM #TransactionTable t2
GROUP BY Department_ID, DATE
< span class =code-keyword> order by Department_id, DATE
I have a table called Transaction which contains following fields
create table TransactionTable(
Department_id int not null,
date date,
comments varchar(20)
);
insert into TransactionTable(Department_id,date,comments) values(1,'2/2/2018','comment 2');
insert into TransactionTable(Department_id,date,comments) values(1,'2/2/2018','comment 3');
insert into TransactionTable(Department_id,date,comments) values(1,'5/5/2017','comment 3');
insert into TransactionTable(Department_id,date,comments) values(1,'4/5/2018','comment 3');
insert into TransactionTable(Department_id,date,comments) values(1,'6/2/2018','comment 5');
insert into TransactionTable(Department_id,date,comments) values(3,'3/4/2018','comment 2');
insert into TransactionTable(Department_id,date,comments) values(3,'5/5/2017','comment 3');
insert into TransactionTable(Department_id,date,comments) values(3,'5/5/2017','comment 2');
insert into TransactionTable(Department_id,date,comments) values(3,'4/5/2018','comment 3');
Department_ID Date Comment
1 2018-02-02 comment 1
1 2018-02-02 comment 2
1 2018-03-04 comment 2
1 2017-02-04 comment 3
1 2017-05-05 comment 3
1 2018-04-05 comment 3
1 2018-06-02 comment 5
3 2018-03-04 comment 2
3 2017-02-04 comment 3
3 2017-05-05 comment 3
3 2017-05-05 comment 2
3 2018-04-05 comment 3
My output shoud look like this
Department_ID Date Comment
1 2018-02-02 comment1,comment2
3 2017-05-05 comment 3,comment 2
What I have tried:
SELECT Department_DATE,DATE,comment1 = STUFF((
SELECT distinct ',' + comment
FROM TransactionTable t1 where t1.Department_ID= t2.Department_ID
FOR XML PATH ('')), 1, 1, '' )
FROM TransactionTable t2
GROUP BY Department_ID,DATE;
解决方案
WITH CTE AS ( SELECT DISTINCT Department_ID,DATE, STUFF((SELECT distinct ',' + comments FROM TransactionTable t1 where t1.Department_ID= t2.Department_ID AND CAST(t1.Date AS DATE)=CAST(t2.Date AS DATE) FOR XML PATH ('')), 1, 1, '' ) AS comments FROM TransactionTable t2) select * from CTE where CHARINDEX(',comm',comments)<>0 -- OR comments LIKE '%,comm%';
SELECT DISTINCT p.Department_id,Date, STUFF((SELECT distinct ',' + p1.comments FROM TransactionTable p1 WHERE p.Department_id = p1.Department_id FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') CommentValue FROM TransactionTable p;
Hope this will help you
Try this
IF OBJECT_ID(N'tempdb..#TransactionTable', N'U') IS NOT NULL DROP TABLE #TransactionTable; create table #TransactionTable( Department_id int not null, date date, comments varchar(20) ); insert into #TransactionTable(Department_id,date,comments) values (1,'2/2/2018','comment 2'), (1,'2/2/2018','comment 3'), (1,'5/5/2017','comment 3'), (1,'4/5/2018','comment 3'), (1,'6/2/2018','comment 5'), (3,'3/4/2018','comment 2'), (3,'5/5/2017','comment 3'), (3,'5/5/2017','comment 2'), (3,'4/5/2018','comment 3'); select * from #TransactionTable order by Department_id,DATE,comments SELECT Department_id,DATE,comment1 = STUFF(( SELECT distinct ',' + comments FROM #TransactionTable t1 where t1.Department_ID= t2.Department_ID and t1.date=t2.date FOR XML PATH ('')), 1, 1, '' ) FROM #TransactionTable t2 GROUP BY Department_ID,DATE order by Department_id,DATE
这篇关于如何根据日期和department_id将多行返回到单个行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文