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

Posted on 2015-02-11
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 48

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 =
        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 48

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.
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


Author Comment

ID: 40609177
I will be in a position to provide real meaningful data and better 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 48

Accepted Solution

PortletPaul earned 500 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 48

Assisted Solution

PortletPaul earned 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime is disastrous for companies and can lead to major hits on a brand, reputation, an…
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…

726 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