PRANIT GHARAT Ответов: 3

Как я могу вернуть несколько строк в одну строку в соответствии с датой и department_id?

У меня есть таблица под названием транзакция, которая содержит следующие поля 
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

Мой вывод будет выглядеть примерно так

Department_ID Дата Комментарий

1 2018-02-02 comment1,comment2
3 2017-05-05 комментарий 3,комментарий 2

Что я уже пробовал:

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;

Santosh kumar Pithani

Прежде чем публиковать какие-либо примеры данных, вы должны быть уверены в этом.

3 Ответов

Рейтинг:
2

Santosh kumar Pithani 

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%';

Рейтинг:
2

Member 13978939

Попробовать это

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

Рейтинг:
0

Member 12893295 

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;


Надеюсь, это вам поможет