Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
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 find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Suggested Courses

609 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