Solved

How to get distinct value with comma separated in SQL?

Posted on 2009-07-07
7
1,645 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
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Query 4 34
T-SQL Convert to PL/SQL 23 62
Convert Oracle data into XML document 2 39
Very interesting Access query problem. 13 44
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now