Solved

How to get distinct value with comma separated in SQL?

Posted on 2009-07-07
7
1,884 Views
Last Modified: 2013-12-07
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
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24791995
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.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 24792021
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

0
 
LVL 14

Accepted Solution

by:
shru_0409 earned 500 total points
ID: 24792056
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

may be this help u
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24792059
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
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24792148
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
0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 31600493
GooD ONE
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24793160
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question