Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Displaying values of a field in 2 columns

Posted on 2011-10-25
24
Medium Priority
?
530 Views
Last Modified: 2012-05-12
Hi,

I have a table field which holds two types of values.
For example:

Member_Guest_id
3434
9565
6332
9778

There is another field called type_cd which tells us what type it is, like member (type_cd = 1), guest (type-cd = 2). Member & guest names come from another table.

Here is how the fields look in the table
Member_Guest_id       Type_cd
3434                                1
9565                                2
6332                                2
9778                                1

When I display the member and guest names (for a company) in two columns, they are being displayed like this:

company_id    Member_guest_id       Member_name             Guest_name
123                        3434                    David Miller
123                        9565                                                          Chris Valer
123                        6332                                                          Lila Woods
123                        9778                    Paul Works

What should I do if I want to avoid the spaces in Member_name & guest_names ?

I want the result to be as follows:

company_id           Member_name             Guest_name
123                          David Miller                 Chris Valer
123                          Paul Works                 Lila Woods
                                         
(I don' t need to display the  Member_guest_id )

Thanks for your help!

0
Comment
Question by:LoveSpring
  • 9
  • 5
  • 5
  • +2
24 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37029085
that is an unusual request
how do you know which guest to connect to which member?
is it random?
what happens if there are more guests than members or the other way around?
0
 

Author Comment

by:LoveSpring
ID: 37029108

Hi momi_sabag,

User requested it to be displayed like this:
company_id           Member_name             Guest_name
123                          David Miller                 Chris Valer
123                          Paul Works                 Lila Woods

When I had written the SQL, the result shows up like this:
company_id    Member_guest_id       Member_name             Guest_name
123                        3434                    David Miller
123                        9565                                                          Chris Valer
123                        6332                                                          Lila Woods
123                        9778                    Paul Works

I was wondering if there is any way to display as the user requested.
If not, I will leave it with blanks in the middle as shown above.

Thanks for the reply.
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 1000 total points
ID: 37029113
in order to show it the way you want
each row has to contain data that is derived according to two different member ids, one with type 1 and one with type 2
in order to do that, you need to decide how you couple those member ids together
otherwise, it is impossible
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:LoveSpring
ID: 37029119
Hi momi_sabag,

We don't have to list the Member_guest_id.

We need to list all the Members and Guests for a company (in this case company id 123)

Thanks.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37029123
it is not possible to do what you want
0
 

Author Comment

by:LoveSpring
ID: 37029134

Hi momi_sabag,

Ok. Thanks.

Can I ask you another question related to this?
if my result is like this

company_id    company_name   Member_guest_id       Member_name             Guest_name
123                      abc                               3434                    David Miller
123                      abc                               9565                                                          Chris Valer
123                      abe                               6332                                                          Lila Woods
123                      abe                               9778                    Paul Works

What can I do to display in the following format? Is this possible?

company_id    company_name   Member_guest_id       Member_name             Guest_name
123                      abc                               3434                    David Miller
                                                                 9565                                                          Chris Valer
                                                                 6332                                                          Lila Woods
                                                                 9778                    Paul Works

Displaying company_id and company_name fields only once.

Is there any way I can accomplish the above?

Thanks.

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37029144
can you post the sql you are currently using to get that result?
0
 

Author Comment

by:LoveSpring
ID: 37029187

Hi momi_sabag,

Here is the query I am using:

SELECT C.C_ID, C.COMP_NAME,
CASE A.TYPE_CD
WHEN 2 THEN B.COMP_NAME
END AS GUESTS
,CASE A.TYPE_CD
WHEN 1 THEN B.COMP_NAME
END AS MEMBERS
FROM
DTEST.TS_MEMBER_GUEST A,
DTEST.TS_FIRM_DTL B,
DTEST.TS_FIRM_DTL C
WHERE
(A.C_ID = C.C_ID AND A.REG_YEAR = C.REG_YEAR)
AND
A.REG_YEAR = 2011 AND A.TYPE_CD IN (1, 2)
AND A.MEMBER_GUEST_ID = B.C_ID
     AND (B.REG_YEAR = 2011)
AND A.DATE_TERMINATED = 0
AND C.DATE_TERMINATED = 0
AND A.STATUS_FLAG = 1
AND A.MEMBER_GUEST_ID < 89999999

Thanks.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37029340
so try this one

with a as (
SELECT C.C_ID, C.COMP_NAME, row_number() over(partition by c.c_id, c.comp_name order by c.c_id) row,
CASE A.TYPE_CD
WHEN 2 THEN B.COMP_NAME
END AS GUESTS
,CASE A.TYPE_CD
WHEN 1 THEN B.COMP_NAME
END AS MEMBERS
FROM
DTEST.TS_MEMBER_GUEST A,
DTEST.TS_FIRM_DTL B,
DTEST.TS_FIRM_DTL C
WHERE
(A.C_ID = C.C_ID AND A.REG_YEAR = C.REG_YEAR)
AND
A.REG_YEAR = 2011 AND A.TYPE_CD IN (1, 2)
AND A.MEMBER_GUEST_ID = B.C_ID
     AND (B.REG_YEAR = 2011)
AND A.DATE_TERMINATED = 0
AND C.DATE_TERMINATED = 0
AND A.STATUS_FLAG = 1
AND A.MEMBER_GUEST_ID < 89999999
)
select case when rown=1 then c_id else '' end c_id,
          case when rown=1 then comp_name else '' end comp_name, guests, memebers
from a
0
 
LVL 2

Expert Comment

by:jkasavan
ID: 37029398
What about using ODBC to connect to the DB2 data in Excel and then create a pivot table.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 37030652
Hi Lovespring,

Can you post the descriptions of your tables.  Your initial request sounds pretty reasonable and all it takes is an identifiable relationship between the rows.

Your original query (reposted below) inner joins the ts_member_guest table to the ts_firm_dtl table (twice).  It looks like the query should return both the member and guest identification on the same row, OR the data is contained in two rows and a careful join and filter of the results back onto itself will produce a result with the member and guest name on the same row.


Kent

SELECT 
  C.C_ID, C.COMP_NAME,
  CASE A.TYPE_CD WHEN 2 THEN B.COMP_NAME END AS GUESTS,
  CASE A.TYPE_CD WHEN 1 THEN B.COMP_NAME END AS MEMBERS
FROM
  DTEST.TS_MEMBER_GUEST A,
  DTEST.TS_FIRM_DTL B,
  DTEST.TS_FIRM_DTL C
WHERE (A.C_ID = C.C_ID AND A.REG_YEAR = C.REG_YEAR)
  AND A.REG_YEAR = 2011 
  AND A.TYPE_CD IN (1, 2)
  AND A.MEMBER_GUEST_ID = B.C_ID
  AND (B.REG_YEAR = 2011)
  AND A.DATE_TERMINATED = 0
  AND C.DATE_TERMINATED = 0
  AND A.STATUS_FLAG = 1
  AND A.MEMBER_GUEST_ID < 8999999

Open in new window

0
 
LVL 27

Expert Comment

by:tliotta
ID: 37035305
company_id    company_name   Member_guest_id       Member_name             Guest_name
123                      abc                               3434                    David Miller
123                      abc                               9565                                                          Chris Valer
123                      abe                               6332                                                          Lila Woods
123                      abe                               9778                    Paul Works


If you can already achieve a result set that looks that way, you should be able to process it with a { GROUP BY company_id. company_name } and maybe something like MAX() over both Member_name and Guest_name columns.

Tom
0
 

Author Comment

by:LoveSpring
ID: 37038098
Hi momi_sabag,

with a as (
SELECT C.C_ID, C.COMP_NAME, row_number() over(partition by c.c_id, c.comp_name order by c.c_id) row

is not being supported on the DB2 version we are using (DB2 version 8.1 on z/OS).

Kdo:
TS_FIRM_DTL C table contains ID (ID), company name (COMP_NAME) and many other details registration date, status, termination date etc.

TS_MEMBER_GUEST table contains company id (ID) which links to Company id in  TS_FIRM_DTL table (primary-foreign key relationship), member_guest_id, type_cd (1= member, 2=guest), and other details like registration date, status, termination date etc.

The query displays all the member and guest name for a company id. The company id, company name are being repeated for each row. I need help is figuring out away to display the company id and name only once.

tliotta:
Cannot do GROUP BY on just 2 fields (it says we need to list all the fields in the select statement in the GROUP BY). Can you post a sample SQL ? or modify the SQL above?

The environment is DB2 8.1 on z/OS.

Thanks for all of your help!
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 37038937
Hi LoveSpring,

Surely one of the tables has something that ties the rows together other than company_id.  Perhaps and event ID, time stamp, or other data object that can be used to identify that two people were there at the same time?


Kent
0
 

Author Comment

by:LoveSpring
ID: 37039065
Hi Kdo,

ID, REG_YEAR are fields common in both TS_FIRM_DTL & TS_MEMBER_GUEST
and these fields tie them together.

Thanks.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 37039087
Hi Lovespring,

So how do you tie members and guests together?  Certainly there has to be more granularity than "these 13 members and their 23 guests during 2010 ".


Kent
0
 

Author Comment

by:LoveSpring
ID: 37039838
Kent,

There is no tie between members and guests.

Each record in TS_MEMBER_GUEST table would be either a member or a guest. Type_cd tells what it is (1=member, 2=guest). TS_FIRM_DTL & TS_MEMBER_GUEST tables are connected by ID, REG_YEAR.

I don't know where the "13 members and their 23 guests during 2010" come from.

I didn't specify it any where.

Thanks.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 37040042

"13 members and their 23 guests" was a generality.

I guess I just don't understand why you don't just display the results directly from the joined objects.  (Or maybe that's the question.)

The code and results suggests that there may be more than 1 row in TS_FIRM_DTL for the same event.  One row joins to TS_FIRM_DTL (via MEMBER_GUEST_ID) to retrieve the member information, and another row joins to TS_FIRM_DTL (also via MEMBER_GUEST_ID) for the guest information.



Kent
0
 

Author Comment

by:LoveSpring
ID: 37040097
It is displaying the results correctly.

My question is, how to display the company id and name only once.


company_id    company_name   Member_guest_id       Member_name             Guest_name
123                      abc                               3434                    David Miller
123                      abc                               9565                                                          Chris Valer
123                      abe                               6332                                                          Lila Woods
123                      abe                               9778                    Paul Works

What can I do to display in the following format? Is this possible?

company_id    company_name   Member_guest_id       Member_name             Guest_name
123                      abc                               3434                    David Miller
                                                                 9565                                                          Chris Valer
                                                                 6332                                                          Lila Woods
                                                                 9778                    Paul Works

Thanks.
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1000 total points
ID: 37040159
Hmm...

I thought that you wanted something like:

company_id           Member_name             Guest_name
123                          David Miller                 Chris Valer
123                          Paul Works                 Lila Woods

Anyway, try the query below.

Kent


SELECT
  CASE when RN = 1 then cast (C_ID as varchar (10)) else ' ' end C_ID,
  CASE when RN = 1 then COMP_NAME else ' 'end comp_name,
  guests,
  members
FROM
(
	SELECT 
	  ROW_NUMBER () OVER (PARTITION BY C_ID) AS RN,
		C.C_ID, 
		C.COMP_NAME,
		CASE A.TYPE_CD WHEN 2 THEN B.COMP_NAME END AS GUESTS,
		CASE A.TYPE_CD WHEN 1 THEN B.COMP_NAME END AS MEMBERS
	FROM
		DTEST.TS_MEMBER_GUEST A,
		DTEST.TS_FIRM_DTL B,
		DTEST.TS_FIRM_DTL C
	WHERE (A.C_ID = C.C_ID AND A.REG_YEAR = C.REG_YEAR)
		AND A.REG_YEAR = 2011 
		AND A.TYPE_CD IN (1, 2)
		AND A.MEMBER_GUEST_ID = B.C_ID
		AND (B.REG_YEAR = 2011)
		AND A.DATE_TERMINATED = 0
		AND C.DATE_TERMINATED = 0
		AND A.STATUS_FLAG = 1
		AND A.MEMBER_GUEST_ID < 8999999
        ORDER BY 3, 1
) t0

Open in new window

0
 
LVL 27

Expert Comment

by:tliotta
ID: 37041337
Cannot do GROUP BY on just 2 fields (it says we need to list all the fields in the select statement in the GROUP BY)

That is correct except that the part in parentheses needs some modification. You don't need to GROUP BY any columns that are processed by a function such as MAX().

Since multiple example output reports have been shown, I simply supplied a conceptual response. The example report that I used included the Member_guest_id column. I did ignore that column because it would not be used for the final output. I understood that you wanted output to look this way:

company_id           Member_name             Guest_name
123                          David Miller                 Chris Valer
123                          Paul Works                 Lila Woods

With that type of output, there is no place for Member_guest_id, so there is no reason to bring it out in the inner result set in the first place. Actually, because company_name isn't in the final output either, there's no reason to have it in the GROUP BY. In that case, it'd just be { GROUP BY company_id } plus the two MAX() columns. The MAX() function is simply a way to say that you want the non-blank value from that column from the inner result set.

The suggestion was based on my assumption that you were actually succeeding in getting the report (i.e., the result set that the report was generated from) that I referenced. I don't know what query you used to get that version of your output.

Tom
0
 

Author Comment

by:LoveSpring
ID: 37041488

Hi Kdo,

The DB2 version I am using is not supporting 'ROW_NUMBER () OVER ('.
I am using DB2 version 8.1 for z/OS.

I found an alternative way.

After saving the result in Excel format, I was able to apply a Macro to remove the duplicate data in complany_id and company_name columns.

May be one day I will find out how to do it all in SQL (in DB2 V9?) .

Thanks for all you help!
0
 
LVL 27

Expert Comment

by:tliotta
ID: 37042455
Sheesh... I still messed that up -- the GROUP BY does need company_name included because there are different company_name values within the company_id=123 group. (But Member_guest_id has no use nor place for it to go.) Too many examples to keep track of...

Tom
0
 
LVL 27

Expert Comment

by:tliotta
ID: 37042464
That is, "...the GROUP BY does need company_name included (in my concept suggestion)...". It's not related to Kent's solution.

Tom
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

810 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