4 Replies - 3318 Views - Last Post: 18 March 2018 - 09:45 PM

#1 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 328
  • Joined: 20-May 16

how to use listagg in sql server?

Posted 18 March 2018 - 07:09 AM

I wrote the following query below. issue is that I am using sql server and it does not have group_Concat function. Any idea how can I do this in sql server?

my query:

    select DISTINCT 
    o.Order_ID, o.Order_Total, sub_Status.StatusName
    FROM [ORDER_TB] o
    left join (
    	select DISTINCT         
    	o.Order_ID, 
    	GROUP_CONCAT(s.name order by s.name) as StatusName
    	FROM [ORDER_TB] o
    	LEFT JOIN [ORDER_STATUS_TB] os ON os.Order_ID = o.Order_ID
    	LEFT JOIN [STATUS_TB] s ON s.Status_ID = os.Status_ID
    	group by o.order_ID
    ) sub_Status on o.Order_ID = sub_Status.Order_ID;



Query output:

    1 | 10 | pending
    1 | 10 | shipped
    2 | 20 | pending
    3 | 10 | pending



i want the following result:

    1 | 10 | pending, shipped
    2 | 20 | pending
    3 | 10 | pending


Is This A Good Question/Topic? 0
  • +

Replies To: how to use listagg in sql server?

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6798
  • View blog
  • Posts: 28,097
  • Joined: 12-December 12

Re: how to use listagg in sql server?

Posted 18 March 2018 - 12:06 PM

A little Google searching, String Aggregation in SQL Server.
Was This Post Helpful? 0
  • +
  • -

#3 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 328
  • Joined: 20-May 16

Re: how to use listagg in sql server?

Posted 18 March 2018 - 02:17 PM

i need some simple way for doing it. maybe like a function that i can use or some thing

and please dont post 1st google search result you found, just bc you want to gain simple points. and try to avoid topics you dont understand. thanks

i already speand alot of time on google

This post has been edited by ikhlas06: 18 March 2018 - 02:22 PM

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6798
  • View blog
  • Posts: 28,097
  • Joined: 12-December 12

Re: how to use listagg in sql server?

Posted 18 March 2018 - 02:58 PM

Yeah, I am not a mind reader. You gave no indication that you had done any searching, nor that you had found and dismissed this solution, nor explained why it wasn't suitable.

But I'll leave you to it.
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15111
  • View blog
  • Posts: 60,452
  • Joined: 12-June 08

Re: how to use listagg in sql server?

Posted 18 March 2018 - 09:45 PM

Maybe ease up on the down votes there. That was a pretty damn helpful link.

Example after reading that link I poked around at the 'for xml path' and found this to be a good proof of concept.

cre ate table #foo(lval int, sval varchar(10))
ins ert into #foo(lval,  sval) values (1 ,  'pending')
in sert into #foo(lval,  sval) values (1 ,  'shipped')
ins ert into #foo(lval,  sval) values (2 ,  'pending')
ins ert into #foo(lval,  sval) values (3 ,  'pending')

select *
from #foo

Select lval,
Substring((Select ',' + sval  
		From #foo b 
		Where b.lval = a.lval 
		For XML Path('')),2,8000) As sval
From  #foo a
Group By  lval

dr op table #foo


lval        sval
----------- ----------
1           pending
1           shipped
2           pending
3           pending

(4 rows affected)

lval        sval
----------- ------------------------------
1           pending,shipped
2           pending
3           pending


Here's the read up on the 'for' work.
https://docs.microso...-xml-sql-server
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1