Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to get distinct value with comma separated in SQL?

Posted on 2009-07-07
7
Medium Priority
?
1,987 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 2000 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

722 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