Solved

How to get distinct value with comma separated in SQL?

Posted on 2009-07-07
7
1,769 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
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!

 

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

Independent Software Vendors: 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

Suggested Solutions

Title # Comments Views Activity
SQL Select in Access 2003 3 42
How to create XML in below format from SQL Query in SQL Server? 3 43
MS SQL GROUP BY 6 72
SQL query to select row with MAX date 7 40
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

735 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