Displaying values of a field in 2 columns

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!

LoveSpringAsked:
Who is Participating?
 
momi_sabagCommented:
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
 
momi_sabagCommented:
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
 
LoveSpringAuthor Commented:

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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LoveSpringAuthor Commented:
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
 
momi_sabagCommented:
it is not possible to do what you want
0
 
LoveSpringAuthor Commented:

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
 
momi_sabagCommented:
can you post the sql you are currently using to get that result?
0
 
LoveSpringAuthor Commented:

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
 
momi_sabagCommented:
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
 
jkasavanCommented:
What about using ODBC to connect to the DB2 data in Excel and then create a pivot table.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
tliottaCommented:
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
 
LoveSpringAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
LoveSpringAuthor Commented:
Hi Kdo,

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

Thanks.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
LoveSpringAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:

"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
 
LoveSpringAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
tliottaCommented:
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
 
LoveSpringAuthor Commented:

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
 
tliottaCommented:
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
 
tliottaCommented:
That is, "...the GROUP BY does need company_name included (in my concept suggestion)...". It's not related to Kent's solution.

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