Solved

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

Posted on 2015-02-11
9
160 Views
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

Enyimba
0
Comment
Question by:Enyimba
  • 5
  • 4
9 Comments
 
LVL 48

Expert Comment

by:PortletPaul
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 (
         select
               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

0
 

Author Comment

by:Enyimba
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?

Thanks

Enyimba
0
 
LVL 48

Expert Comment

by:PortletPaul
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:
Table_1
SSN     PID    FNAME   LNAME
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.
0
 

Author Comment

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

Thanks

Enyimba
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Enyimba
ID: 40613792
PortletPaul,

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.

Thanks

Enyimba
testrecPERIHIST.txt
0
 
LVL 48

Accepted Solution

by:
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.
0
 
LVL 48

Assisted Solution

by:PortletPaul
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?
0
 

Author Comment

by:Enyimba
ID: 40614153
PortletPaul,

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

Enyimba
0
 

Author Comment

by:Enyimba
ID: 40614184
PortletPaul,

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 Regular patching is part of a system administrator's tasks. However, many patches require that the system be in single-user mode before they can be installed. A cluster patch in particular can take quite a while to apply if the machine…
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…
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.:

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now