SQL challeng: How can I do this using SQL? listing data vertically for each cutomer?

Posted on 2015-02-11
Medium Priority
Last Modified: 2015-02-17
Dear gurus,

I have two tables, a client table and a product table. Client table has a one-to-many relations with product. (PKey & Fkey).  Many clients and of course many more products.
I want to read the client table and for each client accumulate up to 10 or more UPC codes for each client and present the report as follows:


Client1#    UPC1, UPC2, UPC3, UPC4, UPC5, UPC6, UPC7, UPC8, UPC9, UPC10
Client2#    UPC1, UPC2, UPC3, UPC4, UPC5, UPC6, UPC7, UPC8, UPC9, UPC10
Client3#    UPC1, UPC2, UPC3, UPC4, UPC5, UPC6, UPC7, UPC8, UPC9, UPC10
Client4#    UPC1, UPC2, UPC3, UPC4, UPC5, UPC6, UPC7, UPC8, UPC9, UPC10
Client5#    UPC1, UPC2, UPC3, UPC4, UPC5, UPC6, UPC7, UPC8, UPC9, UPC10
Client6#    UPC1, UPC2, UPC3, UPC4, UPC5, UPC6, UPC7, UPC8, UPC9, UPC10
Client7#    UPC1, UPC2, UPC3, UPC4, UPC5, UPC6, UPC7, UPC8, UPC9, UPC10
Client8#    UPC1, UPC2, UPC3, UPC4, UPC5, UPC6, UPC7, UPC8, UPC9, UPC10
Client9#    UPC1, UPC2, UPC3, UPC4, UPC5, UPC6, UPC7, UPC8, UPC9, UPC10
Client10#    UPC1, UPC2, UPC3, UPC4, UPC5, UPC6, UPC7, UPC8, UPC9, UPC10

How can this be achieved using SQL, common table expressions or perhaps some other advanced SQL techniques.

I will greatly appreciate any and all development or examples.

Again, thanks

Question by:Enyimba
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 49

Expert Comment

ID: 40604872
Not much is revealed about your tables. For example how do you get from Client to Product? Is there a "sales" table?
I also have to presume that UPC is a "product code" that is different from the fields used to join the various tables.

So, without tables or data, there is assumptions about both.

Not sure when LISTAGG() was included in DB2 but this will help you produce a set of comma separated values

This example from the linked manual page shows how to use it (as part of a grouping query)
  SELECT workdept,
         LISTAGG(lastname, ', ') WITHIN GROUP(ORDER BY lastname)
         AS employees
      FROM emp
      GROUP BY workdept

and applying this to your scenario it might look like this:

select d.clientid, d.productid, LISTAGG(d.UPC , ', ') WITHIN GROUP(ORDER BY d.COUNT_OF)
 from (
               c.clientid, p,productid, p.UPC, count(*) as COUNT_OF
        from client_sales c
        inner join products p on c.productid = p.id
        group by 
              c.clientid, p,productid, p.UPC
       ) d

Open in new window


Author Comment

ID: 40605671
Hello PortletPaul,
Thanks for you response. Sorry I did not add more detail earlier.
What I do have you have assume correctly with the following additions. Consider that my CLIENT table comprises the CLNTID (primary key), UPC (Foreign Key), CLNTNAME and UPC_Code table cosist of UPC, CLNTID, UPC_Desc  Also, there is a one-to-many between the client table and UPC table. The join is on the CLNTID.

Does this help? Can the code you provided be enhanced any further?


LVL 49

Expert Comment

ID: 40606659
Please provide PER TABLE some "sample data"
and then, the "expected result"

a simple parsable list of a few records such as this (or comma separated) is what I suggest for each table:
1       NULL   Gxxxxx  Mxxx
2       NULL   Kxxxx   Txxx
3       NULL   Kxxxx   Txxx
4       NULL   Jxxxxx  Sxxx

Open in new window

It is far easier to comprehend SQL issues with tables AND data, than attempting to describe these conditions (easier for you too in the long run).

oh! & If you use real table names and real field names, then any SQL produced by us will be much more meaningful to you.
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.


Author Comment

ID: 40609177
I will be in a position to provide real meaningful data and better description...so please bear with me thru this weekend...



Author Comment

ID: 40613792

Sorry it took some to get back to this thread.

The requirement that I am working with has changed a litttle bit. Instead of comma delimited column/field, I now need the columns to be seperated by the pipe  "|" delimiter. In addition, there are formating rules to be applied to the columns. i have attached a text file showing record layout, the effective lenght of the column, the rules for formating the column and the expected result.

I hope the text file makes sense ad I look forward to your next response.


LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 40613928
That is utterly different! and the disadvantage to you is that now only a few people will be looking at this question. You may be better off asking a new one.

The other aspect to this is that the very best way to get SQL answers is to provide "sample data" and the "expected result" where the sample data reflects both the the table structure and the data types of the real situation. Then the expected result should be derived from that sample data. The format you provide is just that, a format.

I only have a small amount of time for this voluntary work and I'm not sure when I can re-look at this question.
LVL 49

Assisted Solution

PortletPaul earned 2000 total points
ID: 40613950
I see that the file you have provided uses the terms "sample data" and "expected result"... but I'm afraid that isn't what I would regard as either. Helpful as a format specification is, it does not tell anything about the tables and source fields and without information on those I can't produce a query.

This is the only detail have about the tables:

"I have two tables, a client table and a product table ..."

why not supply the full table names and their field names? and some rows of data from those tables?

Author Comment

ID: 40614153

Again, thanks for your response. Perhaps, you are right, I should ask this question again with perhaps a differrent heading. But just to clarify, yes initially, I was made to believe I will need to extract the information from two tables but this was later changed. Another team will extract the data, pass it through one or two processes and in the end hand over the extracted data to me to perform he final "massaging". As it stand, I no longer have to do any table joins etc. I only have to do a select function, "massage" the data and output as required.

Now, I will close this thread and open it as a new thread...

Again, thanks


Author Comment

ID: 40614184

Forgot to mention, the sample data that I showed is just about what the sample data will look like. Really! I can try to get more sample data, but every thing will come about the same layout. I am not using column1 thru column17 just to shorten the names as we use very long name in this shop and also to make it such any SQL provided will not have to use these long names.

Featured Post

Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

Question has a verified solution.

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

I have been running these systems for a few years now and I am just very happy with them.   I just wanted to share the manual that I have created for upgrades and other things.  Oooh yes! FreeBSD makes me happy (as a server), no maintenance and I al…
Introduction People like FTP.  It's a solid, stable, robust protocol for quickly transferring files between two hosts using TCP/IP.  In most cases it's much faster than SMB or CIFS, and certainly much easier to set up between organizations.  This…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

762 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