By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,965 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,965 IT Pros & Developers. It's quick & easy.

How to merge rows from a Virtual Table, based on a foreign key

P: n/a
The following is the sample result of a large SQL SELECT Statement, from a single table. I have used --- to show the X values under each column

CODE------XX-------------YY-----------------ZZ
AD------------X-----------------------------------
AD-----------------------------------------------X
AD---------------------------X--------------------
CD------------X-----------------------------------
CD-----------------------------------------------X
CD---------------------------X--------------------

I would like to further refine the above results by merging onto a single row for each CODE. Example below.

Please note that each row for each code, will have only one value. So, using a CONCAT feature would be fine for me. I am at a loss to identify the parent SQL Statement to merge the data as below

CODE--XX--YY--ZZ
AD-------X---X---X
CD-------X---X---X

Request help from folks on this forum
Oct 19 '10 #1
Share this Question
Share on Google+
1 Reply


amitpatel66
Expert 100+
P: 2,367
Try this:

Expand|Select|Wrap|Line Numbers
  1. SQL> with t as (select 'AD' col1,'X' xx,NULL yy,NULL zz from dual
  2.   2  union select 'AD',NULL,NULL,'X' from dual
  3.   3  union select 'AD',NULL,'X',NULL from dual
  4.   4  union select 'CD','X',NULL,NULL from dual
  5.   5  union select 'CD',NULL,'X',NULL from dual)
  6.   6  SELECT col1,MAX(xx),MAX(yy),MAX(zz) FROM t
  7.   7  GROUP BY col1;
  8.  
  9. CO M M M
  10. -- - - -
  11. AD X X X
  12. CD X X
  13.  
  14. SQL> 
  15.  
Oct 19 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.