Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View Query (Oracle SQL Developer 4.2.0)
icon3.gif  Materialized View Query [message #676888] Thu, 25 July 2019 07:48 Go to next message
baboyjames
Messages: 1
Registered: July 2019
Junior Member
Hi,

I'm new to creating queries and in this query not sure where to start.

I have this data on a table (4 columns), need to get the attached output using a query (materialized view).

EMAIL FIRST_NAME LAST_NAME TEAM_PRODUCTS
james.david@swimph.net James David Goggles, Fins, Kickboard
shiela.marie@swimph.net Shiela Marie Goggles, Kickboard
kurt.john@swimph.net Kurt John Goggles, Pullbouy
sherryl.anne@swimph.net Sherryl Anne Goggles

Products need to be separated and create a new row for each from the owner.

Not really sure if this is possible.

Hope someone can help me on this.

Thank you!
  • Attachment: OUTPUT.csv
    (Size: 0.38KB, Downloaded 106 times)

[Updated on: Thu, 25 July 2019 08:08]

Report message to a moderator

Re: Materialized View Query [message #676889 is a reply to message #676888] Thu, 25 July 2019 07:59 Go to previous messageGo to next message
BlackSwan
Messages: 26595
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Materialized View Query [message #676890 is a reply to message #676888] Thu, 25 July 2019 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 66554
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: Materialized View Query [message #677080 is a reply to message #676890] Fri, 16 August 2019 06:22 Go to previous message
Bill B
Messages: 1889
Registered: December 2004
Senior Member
CREATE TABLE TEST_TABLE
(
  EMAIL          VARCHAR2(50 BYTE),
  FIRST_NAME     VARCHAR2(50 BYTE),
  LAST_NAME      VARCHAR2(50 BYTE),
  TEAM_PRODUCTS  VARCHAR2(500 BYTE)
);

Insert into TEST_TABLE
   (EMAIL, FIRST_NAME, LAST_NAME, TEAM_PRODUCTS)
 Values
   ('james.david@swimph.net', 'James', 'David', 'Goggles');
Insert into TEST_TABLE
   (EMAIL, FIRST_NAME, LAST_NAME, TEAM_PRODUCTS)
 Values
   ('james.david@swimph.net', 'James', 'David', 'Fins');
Insert into TEST_TABLE
   (EMAIL, FIRST_NAME, LAST_NAME, TEAM_PRODUCTS)
 Values
   ('james.david@swimph.net', 'James', 'David', 'Kickboard');
Insert into TEST_TABLE
   (EMAIL, FIRST_NAME, LAST_NAME, TEAM_PRODUCTS)
 Values
   ('shiela.marie@swimph.net', 'Shiela', 'Marie', 'Goggles');
Insert into TEST_TABLE
   (EMAIL, FIRST_NAME, LAST_NAME, TEAM_PRODUCTS)
 Values
   ('shiela.marie@swimph.net', 'Shiela', 'Marie', 'Kickboard');
Insert into TEST_TABLE
   (EMAIL, FIRST_NAME, LAST_NAME, TEAM_PRODUCTS)
 Values
   ('kurt.john@swimph.net', 'Kurt', 'John', 'Goggles');
Insert into TEST_TABLE
   (EMAIL, FIRST_NAME, LAST_NAME, TEAM_PRODUCTS)
 Values
   ('kurt.john@swimph.net', 'Kurt', 'John', 'Pullbouy');
Insert into TEST_TABLE
   (EMAIL, FIRST_NAME, LAST_NAME, TEAM_PRODUCTS)
 Values
   ('sherryl.anne@swimph.net', 'Sherryl', 'Anne', 'Goggles');
COMMIT;

SELECT Email,
         First_name || ' ' || Last_name                                         Name,
         LISTAGG (Team_products, ', ') WITHIN GROUP (ORDER BY Team_products)    AS Products
    FROM Test_table
GROUP BY Email, First_name || ' ' || Last_name
ORDER BY Email;
Previous Topic: SQL Tuning instead of Cross Join
Next Topic: Data spliting
Goto Forum:
  


Current Time: Thu Sep 19 14:29:31 CDT 2019