Solved

How to get distinct value with comma separated in SQL?

Posted on 2009-07-07
7
1,703 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

825 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