Click here to Skip to main content
14,148,895 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
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;
Posted
Updated 19-Sep-18 21:21pm
v2
Comments
Santosh kumar Pithani 4-Sep-18 2:53am
   
Before posting any sample data you must be sure about on it.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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%'; 
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190518.1 | Last Updated 20 Sep 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100