• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1914
  • Last Modified:

Select Comma Separated List

I want to select comma separated enames as single value for salary = 5000 from employee table. Please give me a single query to get this.
0
k_murli_krishna
Asked:
k_murli_krishna
  • 4
  • 3
  • 2
  • +1
1 Solution
 
momi_sabagCommented:
can you put an example ot what you need ?
if i understand you correctly, if your table contains

emp1, 5000
emp2, 5000
emp3, 6000
emp4, 1000
emp5, 5000

you want a select statement that will return - emp1,emp2,emp5 ?
0
 
tliottaCommented:
I _think_ the question is looking for:

  select name1 CONCAT ', ' CONCAT name2 CONCAT ', ' CONCAT name3 as "Name" from myfile

Columns may need to be trimmed or other manipulation.

Tom
0
 
tliottaCommented:
More precisely would be:

   select name1 CONCAT ', ' CONCAT name2 CONCAT ', ' CONCAT name3 as "Name" from myfile where salary = 5000

Tom
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
k_murli_krishnaAuthor Commented:
momi, what you understood is spot on. Please provide the query.
tliotta: name1, name2 etc are not separate columns but the way momi has laid the data out i.e. ename is sinlge name column.
0
 
tliottaCommented:
If that's the case, then I'm not sure an answer would be useful. It sounds as if it's an incorrect problem. The best answer might be that you shouldn't put data like that into DB2 in the first place.

Perhaps the situation is that data is being received as a .CSV and it isn't being put into DB2 correctly. A proper answer should indicate how to eliminate the improperly formatted data so that such a query is never needed.

I'm concerned that the expectation is that a DB2 query can be used against any .CSV file or that a .CSV format is a reasonable format for DB2 rows. The potential result might be a table that is a very poor performer.

Tom
0
 
momi_sabagCommented:
you can get the output you wish using recursive sql
i'm trying to work on it, but i'm a bit rusty with recursive sql so it might take me some time
0
 
vermakrishCommented:
hi,
  Try ..
     SELECT ENAME ||','  FROM EMPLOYEE WHERE salary = 5000 ;

this would give u the following o/p
  ename1  ,
  ename2  ,
   ename3  .......


Hopefully it will solve ur problem.
0
 
k_murli_krishnaAuthor Commented:
momi, I tried with recursive SQL i.e. using WITH clause but this is used for reusability rather than a true recursion used in calculating factorial of a number. Please do provide me an example of recursive SQL that you are suggesting.
0
 
momi_sabagCommented:
hi
try to look here
  http://www.experts-exchange.com/Database/DB2/Q_22642179.html
it has an example
0
 
momi_sabagCommented:
if you can't figure it out, i'll help you of course
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now