1

Suppose I have a table like this:

JOB_NUMBER |     PART  
-----------+------------
JOB 1      |     PART A  
JOB 2      |     PART B  
JOB 2      |     PART C  
JOB 4      |     PART D  
JOB 3      |     PART E

In my Pervasive database I need to run a command that will give me this output:

JOB_NUMBER |    PART
-----------+--------------------
JOB 1      |    PART A
JOB 2      |    PART B, PART C
JOB 4      |    PART D
JOB 3      |    PART E

SQL PSEUDO Code

SELECT JOB_NUMBER, CONCATENATE_GROUP_BY(PART) as PART FROM JOB_NUMBER_TABLE
GROUP BY JOB_NUMBER

I have found some post on Stackoverflow.com people handling this type of thing in Orcal and MySql, I am SQL amateur and need some help here.

Links for reference:
Concatenate many rows into a single text string?
Concatenate and group multiple rows in Oracle

Community
  • 1
  • 1
Mike Sportsman
  • 120
  • 2
  • 10
  • 1
    I doubt this is possible using pure P-SQL. Have you considered solving this problem in your application instead? – Dan Nov 19 '14 at 14:20
  • The application is kind of the problem. I need to bring this table into a Crystal report. The problem is when I link to the other tables because of the multiple job number, it is causing my other data to be duplicated for each combination of the Job and part number. Other words I need a 1 to 1 relation. I thought if I could do it on the Data side it would simplify my work in Crystal. – Mike Sportsman Nov 19 '14 at 14:34
  • Perhaps you could somehow split your data up into one query that gets all the distinct JOB_NUMBER's, and another query that looks up the PARTs for a given job_number? – Dan Nov 19 '14 at 14:41
  • One the surface that sounds like a good idea, but wouldn't that give me the same thing in the end? For example if I looked up Job 2 from the separate part table that would still return 2 rows. Part B, and Part C – Mike Sportsman Nov 19 '14 at 14:50
  • I know nothing about Crystal reports, but if it's anything like MS Reporting Services, you should be able to group separately on multiple datasets... – Dan Nov 19 '14 at 14:55
  • Dan - Thanks for your help in getting me thinking in the right direction. I was able to get what I need in crystal, by slightly altering my approach and adding some logic. Not as slick as an SQL call would have been, but you know how it goes;-) Thanks once more... – Mike Sportsman Nov 19 '14 at 16:55

0 Answers0