-2

I am using Microsoft SQL server. I have a select statement that has number of columns queried from joining more than 4 tables, and the result table is shown as below,

column1 column2 column3 column4
1 File1 Table1 Column1
1 File1 Table1 Column2
1 File1 Table2 Column1

Here, the values of Column4 are distinct that must be grouped and the result that I require is,

column1 column2 column3 column4
1 File1 Table1 Column1, Column2
1 File2 Table2 Column1
Manikandan
  • 589
  • 1
  • 9
  • 18
  • Does this answer your question? [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) – Ocaso Protal May 05 '23 at 12:13

1 Answers1

0

you can use STRING_AGG

select column1,column2,column3,STRING_AGG(column4,',') as column4
from adds
group by column1,column2,column3

base Data

create table adds(
column1 varchar(100),   column2 varchar(100),   column3 varchar(100),   column4 varchar(100)
)
insert into adds values
(1, 'File1',    'Table1'    ,'Column1')
,(1,'File1',    'Table1'    ,'Column2')
,(1,'File1',    'Table2'    ,'Column1')
abolfazl sadeghi
  • 2,277
  • 2
  • 12
  • 20
  • In this approach, we need to add the every other column as group by columns. Do we have any other approach, so that, we dont need to group by with so many columns? – Manikandan May 06 '23 at 13:27
  • There are other methods, but this method is easier and better You can use CTE and Windows Faction like this link https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings – abolfazl sadeghi May 06 '23 at 13:48