asked on

How to get distinct value with comma separated in SQL?

Hi,

I have a query which will give the below output.

LINE_NUMBER      MIN_1      MIN_2      MIN_3
      1      200      300      400
      2      200      300      400
      3      200      300      400
      4      100      200      400
      5      300      600      700
      6      100      200      400

In the above output, i have a line_number column will fetch the min_1, min_2, min_3 values. If you observe the output some of the line_number values contains the same row. For example line_number 1,2,3 are same and 4,6 are same and 5 is one row. I need the same in the output. I mean In my result i want to show the distinct of min_1, min_2, _min_3 values with comma spearated line_number like below. Can we achieve through SQL Queries or should we go for PL/SQL?

LINE_NUMBER      MIN_1      MIN_2      MIN_3
      1,2,3      200      300      400
      4,6      100      200      400
      5      300      600      700

Can anyone share with your thoughts?

Thanks

pssandhu

You can create a UDF to return a comma seperated value. Check a similar post I made here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24537451.html
Hope this helps
P.

Pratima

try this

select OLD.MIN_1,OLD.MIN_2,OLD.MIN_3,
 (SELECT LEFT(Cast(LINE_NUMBER as varchar(1000)),LEN(Cast(LINE_NUMBER as varchar(1000)))-1) As LINE_NUMBER
 
FROM (
 
SELECT Cast(LINE_NUMBER as varchar(1000)) + ','
 
FROM TABLENAME WITH (NOLOCK) Where TABLENAME.File_NAme = OLD.File_name
 
            FOR XML PATH('')
 
)
 
il(LINE_NUMBER))
 
from dbo.TABLENAME OLD with (nolock) 
Group by OLD.MIN_1,OLD.MIN_2,OLD.MIN_3

Open in new window

ASKER CERTIFIED SOLUTION

shru_0409

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Berger

ASKER

Hi,

Thanks for your response. But i am using here Oracle SQL. How can i convert that into Oracle SQL. I am not good in MS SQL.

Thanks

Steve Berger

ASKER

Hi,
SELECT LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || LINE_NUMBER)), '/x/text()'),',' ) "min"
MIN_1 ,MIN_2,MIN_3
FROM table_name
GROUP BY MIN_1 ,MIN_2,MIN_3

The above query is working fine. Can you explain the concept of this? I never heard about this ever before.

Thanks

Steve Berger

ASKER

GooD ONE

Steve Berger

ASKER

Hi,

In my query i am using inline view to get min_1, min_2, min_3 values and given alias to that. When i group by with that alias name it is throwing error. Because alias is not allowed in group by clause. Can you provide me a way?

Thanks