Home » SQL & PL/SQL » SQL & PL/SQL » Please help me in using pivot and concatenate together.
Please help me in using pivot and concatenate together. [message #224393] Wed, 14 March 2007 02:50 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
HI Experts,

My sql I have uploaded my sql which has the concatenate function and pivot sql. Please help me as I am not able to get the output correctly.

I want the ro_no to be separated according to the ro_dtime but what happens is if there is a 4 ro_no opened in 2004 and 2 ro_no opened in 2006 what happens is all the 6 ro_no is displaying in 2004 and 2006. I want the output like ro_no pertain to 2004 in 2004 and ro_no pertain to 2006 in 2006 column.

I have also given some sample data. Please anyone help me.

[Updated on: Thu, 15 March 2007 01:56]

Report message to a moderator

Please anyone kindly help me in this SQL to have my output (merged) [message #224635 is a reply to message #224393] Wed, 14 March 2007 20:21 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
My current sql is like this

select
b.LIC_NO,TO_CHAR(a.ro_dtime,'YYYY') RO_DTIME,A.RO_NO
from MSS_RO_HDR a, Mss_V_car_records b
WHERE a.ro_dtime between '01-JAN-2000' AND '01-JAN-2007'
AND a.CARD_ID=b.ID
GROUP BY b.LIC_NO,TO_CHAR(A.RO_DTIME,'YYYY'),a.ro_no;

My current output is like this

LIC_NO RO_DTIME RO_NO
GW9896T 2004 RA0044874
GW9896T 2004 RA0047489
GW9896T 2005 RA0056049
GW9896T 2005 RA0056216
GW9896T 2006 RA0066809
GW9896T 2006 RA0069883
GY694H 2004 RA0053667
GY694H 2004 RA0053799
GY694H 2005 RA0054237
GY694H 2005 RA0054571
GY694H 2006 RA0071338
GY694H 2006 RA0079215

My required Output is like this:

LIC_NO 2004 2005 2006
GW9896T RA0044874,RA0047489 RA0056049,RA0056216 RA0066809,RA0069883
GY694H RA0053667,RA0053799 RA0054237,RA0054571 RA0071338,RA0079215

Re: Please anyone kindly help me in this SQL to have my output (merged) [message #224786 is a reply to message #224635] Thu, 15 March 2007 08:23 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Search for pivot, dates cannot be between character strings (it's equivalent to asking if an apple heavier than a dream), and moved to newbies.

[Updated on: Thu, 15 March 2007 08:25]

Report message to a moderator

Re: Please anyone kindly help me in this SQL to have my output (merged) [message #224946 is a reply to message #224786] Fri, 16 March 2007 02:08 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
reported message

Reported By: kumarvk
On: Fri, 16 March 2007 03:06
In: SQL & PL/SQL SQL & PL/SQL Newbies Please anyone kindly help me in this SQL to have my output (merged)
Reason: What made you to move mine to newbies. There is no rule in oracle that date cannot use character strings. ok


This is NOT an expert question, it is a question that has been asked here many times before. Have you followed the advice?

Do not report again, if you have questions you can PM one of the moderators.

MHE
Re: Please anyone kindly help me in this SQL to have my output (merged) [message #224966 is a reply to message #224946] Fri, 16 March 2007 03:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The reason that you shouldn't use character strings as dates is that it leaves you entirely at the mercy of whatever NLS date format your current client software happens to use.
If someone else with a different setting tries to run your code, they'll either get errors if they're lucky, or possibly totally wrong results.

Is that a good enough reason for you to start using TO_DATE(<string>,<format mask>)?
Re: Please anyone kindly help me in this SQL to have my output (merged) [message #225072 is a reply to message #224635] Fri, 16 March 2007 22:29 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
-- tables and data:
SCOTT@10gXE> CREATE TABLE mss_v_car_records
  2    (id	  NUMBER,
  3  	lic_no	  VARCHAR2 (7))
  4  /

Table created.

SCOTT@10gXE> INSERT ALL
  2  INTO mss_v_car_records (id, lic_no) VALUES (1, 'GW9896T')
  3  INTO mss_v_car_records (id, lic_no) VALUES (2, 'GY694H')
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@10gXE> CREATE TABLE mss_ro_hdr
  2    (card_id   NUMBER,
  3  	ro_dtime  DATE,
  4  	ro_no	  VARCHAR2 (9))
  5  /

Table created.

SCOTT@10gXE> INSERT ALL
  2  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (1, SYSDATE-1097, 'RA0044874')
  3  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (1, SYSDATE-1096, 'RA0047489')
  4  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (1, SYSDATE-732,  'RA0056049')
  5  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (1, SYSDATE-731,  'RA0056216')
  6  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (1, SYSDATE-367,  'RA0066809')
  7  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (1, SYSDATE-366,  'RA0069883')
  8  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (2, SYSDATE-1095, 'RA0053667')
  9  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (2, SYSDATE-1094, 'RA0053799')
 10  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (2, SYSDATE-730,  'RA0054237')
 11  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (2, SYSDATE-729,  'RA0054571')
 12  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (2, SYSDATE-365,  'RA0071338')
 13  INTO mss_ro_hdr (card_id, ro_dtime, ro_no) VALUES (2, SYSDATE-364,  'RA0079215')
 14  SELECT * FROM DUAL
 15  /

12 rows created.


-- view:
SCOTT@10gXE> CREATE OR REPLACE VIEW your_view AS
  2  SELECT b.lic_no,
  3  	    TO_CHAR (a.ro_dtime, 'YYYY') ro_dtime,
  4  	    a.ro_no
  5  FROM   mss_ro_hdr a, mss_v_car_records b
  6  WHERE  a.card_id = b.id
  7  AND    a.ro_dtime >= TO_DATE ('01-JAN-2000', 'DD-MON-YYYY')
  8  AND    a.ro_dtime <  TO_DATE ('01-JAN-2007', 'DD-MON-YYYY')
  9  /

View created.

SCOTT@10gXE> SELECT * FROM your_view
  2  /

LIC_NO  RO_DTIME RO_NO
------- -------- ------------------------------------------------------------
GW9896T 2004     RA0044874
GW9896T 2004     RA0047489
GW9896T 2005     RA0056049
GW9896T 2005     RA0056216
GW9896T 2006     RA0066809
GW9896T 2006     RA0069883
GY694H  2004     RA0053667
GY694H  2004     RA0053799
GY694H  2005     RA0054237
GY694H  2005     RA0054571
GY694H  2006     RA0071338
GY694H  2006     RA0079215

12 rows selected.


-- function to conctenate values in Oracle 8i:
SCOTT@10gXE> CREATE OR REPLACE FUNCTION concatenate
  2    (p_key_name	 IN VARCHAR2,
  3  	p_key_value	 IN VARCHAR2,
  4  	p_col_to_concat  IN VARCHAR2,
  5  	p_table_name	 IN VARCHAR2,
  6  	p_separator	 IN VARCHAR2 DEFAULT ',')
  7    RETURN		    VARCHAR2
  8  AS
  9    TYPE weak_ref_cur IS REF CURSOR;
 10    v_cur		    weak_ref_cur;
 11    v_value		    VARCHAR2 (4000);
 12    v_string 	    VARCHAR2 (4000);
 13  BEGIN
 14    OPEN v_cur FOR
 15  	   'SELECT ' || p_col_to_concat
 16    || ' FROM '   || p_table_name
 17    || ' WHERE '  || p_key_name || ' = :b_key_value'
 18    || ' ORDER BY :b_col_to_concat'
 19    USING p_key_value, p_col_to_concat;
 20    LOOP
 21  	 FETCH v_cur INTO v_value;
 22  	 EXIT WHEN v_cur%NOTFOUND;
 23  	   v_string := v_string || p_separator || v_value;
 24    END LOOP;
 25    CLOSE v_cur;
 26    RETURN LTRIM (v_string, p_separator);
 27  END concatenate;
 28  /

Function created.

SCOTT@10gXE> SHOW ERRORS
No errors.


-- query that selects from view and uses function to concatenate values:
SCOTT@10gXE> COLUMN ro_dtime FORMAT A8
SCOTT@10gXE> COLUMN ro_no    FORMAT A60
SCOTT@10gXE> SELECT lic_no, ro_dtime,
  2  	    concatenate
  3  	      ('lic_no || ro_dtime',
  4  	       lic_no || ro_dtime,
  5  	       'ro_no',
  6  	       'your_view') AS ro_no
  7  FROM   your_view
  8  GROUP  BY lic_no, ro_dtime
  9  ORDER  BY lic_no, ro_dtime
 10  /

LIC_NO  RO_DTIME RO_NO
------- -------- ------------------------------------------------------------
GW9896T 2004     RA0044874,RA0047489
GW9896T 2005     RA0056049,RA0056216
GW9896T 2006     RA0066809,RA0069883
GY694H  2004     RA0053667,RA0053799
GY694H  2005     RA0054237,RA0054571
GY694H  2006     RA0071338,RA0079215

6 rows selected.


-- query that pivots concatenated values using above query as inline view:
SCOTT@10gXE> COLUMN "2004" FORMAT A19
SCOTT@10gXE> COLUMN "2005" FORMAT A19
SCOTT@10gXE> COLUMN "2006" FORMAT A19
SCOTT@10gXE> SELECT lic_no,
  2  	    MAX (DECODE (ro_dtime, '2004', ro_no)) AS "2004",
  3  	    MAX (DECODE (ro_dtime, '2005', ro_no)) AS "2005",
  4  	    MAX (DECODE (ro_dtime, '2006', ro_no)) AS "2006"
  5  FROM   (SELECT lic_no, ro_dtime,
  6  		    concatenate
  7  		      ('lic_no || ro_dtime',
  8  		       lic_no || ro_dtime,
  9  		       'ro_no',
 10  		       'your_view') AS ro_no
 11  	     FROM   your_view)
 12  GROUP  BY lic_no
 13  ORDER  BY lic_no
 14  /

LIC_NO  2004                2005                2006
------- ------------------- ------------------- -------------------
GW9896T RA0044874,RA0047489 RA0056049,RA0056216 RA0066809,RA0069883
GY694H  RA0053667,RA0053799 RA0054237,RA0054571 RA0071338,RA0079215

SCOTT@10gXE> 

Previous Topic: how to parse a string
Next Topic: Insert result of SELECT * into table without knowing column names (merged)
Goto Forum:
  


Current Time: Wed Dec 07 20:32:30 CST 2016

Total time taken to generate the page: 0.05955 seconds