oracle queries needed

Submitted by ashishkj on Tue, 2020-12-29 04:46
articles: 
SQL & PL/SQL

I want SQL queries for these two scenarios

Q. 1) Anagrams of a string i.e. let a name is RAMESH. So all combinations of letters of word RAMESH.
Probably a procedure needs to be written for this eg RAMESH, RHSEMA. There will be 6*5*4*3*2*1 combinations

Q.2 ) There is one table PERSON as follows:

PERSON_KEY CONTACT_TY CONTACT_DETAIL PRIORITY UPDATE_DATE
12 LANDLINE 10234126 1 13/8/2020
12 MOBILE 81342122 1 1/8/2020
12 WEBSITE www.amazon.com 1 13/1/2020
14 MOBILE 6467433 1 23/3/2020

Please write a sql query which can provide the output as follows

PERSON_KEY LANDLINE MOBILE WEBSITE
14 6467433
12 10234126 81342122 www.amazon.com

I tried the following query for the first scenario

declare
cn_string constant varchar2(3) := 'HER';
begin
for i in 1..3
loop
for j in 1..3
loop
for k in 1..3
loop
if i != j
and j != k
and k != i
then
dbms_output.put_line
( substr(cn_string,i,1) ||
substr(cn_string,j,1) ||
substr(cn_string,k,1)
);
end if;
end loop;
end loop;
end loop;
end;
/

I am able to work only for a 3 character string and not for more than that

For Q.2 I am not able to get any idea. Please advise

»

Comments

Submitted by ashishkj on Tue, 2020-12-29 04:46.

I tried but not able to get the right solution

»

Submitted by Tak on Tue, 2021-02-02 16:04.

This is a very common interview/test question.

Here is one solution.

CREATE OR REPLACE PROCEDURE anagram( p_string IN VARCHAR2 )
IS
  PROCEDURE a( p_string IN VARCHAR2, p_prefix IN VARCHAR2)
  IS
    l_len NUMBER;
  BEGIN
    l_len := LENGTH( p_string );
    IF (1=l_len) THEN
      dbms_output.put_line(p_prefix || p_string );
    ELSE
      FOR i IN 1 .. l_len LOOP
        a( SUBSTR(p_string, 1, i-1) || SUBSTR(p_string, i+1), p_prefix || SUBSTR(p_string, i, 1));
      END LOOP;
    END IF;
  END;
BEGIN
  IF (p_string IS NULL) THEN
    RAISE_APPLICATION_ERROR(-20001, 'string parameter required');
  END IF;
  
  a( p_string, NULL);
END;

»

Submitted by Tak on Wed, 2021-02-03 17:20.

Have a look at the PIVOT clause.

»