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

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

Who is Participating?
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.
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

EnyimbaAuthor Commented:
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?


Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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.
EnyimbaAuthor Commented:
I will be in a position to provide real meaningful data and better description...so please bear with me thru this weekend...


EnyimbaAuthor Commented:

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.


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?
EnyimbaAuthor Commented:

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

EnyimbaAuthor Commented:

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.
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.

All Courses

From novice to tech pro — start learning today.