How can I return multiple rows into single rows as per date and department_id?

Question

1.00/5 (1 vote)

See more:

I have a table called Transaction which contains following fields

SQL

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:

SQL

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 3-Sep-18 19:44pm

PRANIT GHARAT

Updated 19-Sep-18 21:21pm

Santosh kumar Pithani

v2

Add a Solution

Comments

Santosh kumar Pithani 4-Sep-18 2:53am

Before posting any sample data you must be sure about on it.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Santosh kumar Pithani · Answer 1 · 2018-09-03T20:51:00

SQL

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

Member 12893295 · Answer 2 · 2018-09-19T21:21:00

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

Member 13978939 · Answer 3 · 2018-09-10T00:44:00

Try this

SQL

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

How can I return multiple rows into single rows as per date and department_id?

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0