Forum Stats

  • 3,874,089 Users
  • 2,266,676 Discussions
  • 7,911,721 Comments

Discussions

Print content of codepage conversion (cntd)

531871
531871 Member Posts: 315
edited Aug 5, 2009 5:46AM in SQL & PL/SQL
Hello

Given the sql:
select  tegn,  convert_back, 
        rn chr, upper(substr(dump(tegn,16),instr(dump(tegn,16),':')+2)) hex, 
        upper(substr(dump(convert(tegn, 'we8BS2000', 'WE8ISO8859P1'),10),instr(dump(convert(tegn, 'we8BS2000', 'WE8ISO8859P1'),10),':')+1)) chr_BS2000 ,
       upper(substr(dump(convert(tegn, 'we8BS2000', 'WE8ISO8859P1'),16),instr(dump(convert(tegn, 'we8BS2000', 'WE8ISO8859P1'),16),':')+1)) Hex_BS2000 ,
       case when convert_back = tegn then null else '... OBS ...' end obs
from (select level rn, chr(level) tegn, convert(convert(chr(level), 'we8BS2000', 'WE8ISO8859P1'),'WE8ISO8859P1','we8BS2000') convert_back
from dual connect by level <= 255);
It would be very nice to have a maxtrix print of a code page like this:
......    00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
------------------------------------------------------------------------------------
00-0F     00 01 02 03 37 2D 2E 2F 16 05 15 0B  0C 0D 0E 0F
10-1F     10 11 12 13 3C 3D 32 26 18 .......
20-2F
F0-FF
-------------------------------------------------------------------------------------
......    00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
How do I chunk up the 256 lines resultset from the above SQL and make it into 16*16 lines the nicest way?

Best regards
Mette
Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,745 Red Diamond
    Answer ✓
    Hi, Mette,

    Displaying one column from several rows as several columns on one row is called pivoting.
    Search for "pivot" for a description and examples.
    In order to pivot, you must have the value to be displayed (hex_bs25000 in your case) on the same row with something that indicates what row and column of the results will receive that value. The last character of hex is good for the column. For the row, you could you the first character of hex, but I find it easier to derrive it from the rn column in your original query.
    COLUMN	"00"	FORMAT A2
    COLUMN	"01"	FORMAT A2
    COLUMN	"02"	FORMAT A2
    COLUMN	"03"	FORMAT A2
    COLUMN	"04"	FORMAT A2
    -- ...
    COLUMN	"0F"	FORMAT A2
    
    WITH	unpivoted	AS
    (
    	SELECT	FLOOR (rn/16)	AS r_num
    	,	LPAD ( upper(substr(dump(tegn,16),instr(dump(tegn,16),':')+2))
    		     , 2
    		     , '0'
    		     )		AS hex
    	,	LPAD ( LTRIM (upper(substr(dump(convert(tegn, 'we8BS2000', 'WE8ISO8859P1'),16),instr(dump(convert(tegn, 'we8BS2000', 'WE8ISO8859P1'),16),':')+1)))
    		     , 2
    		     , '0'
    		     )		AS hex_bs25000
    	FROM	(select level rn, chr(level) tegn, convert(convert(chr(level), 'we8BS2000', 'WE8ISO8859P1'),'WE8ISO8859P1','we8BS2000') convert_back
    from dual connect by level <= 255)
    )
    SELECT	  MIN (hex) || '-'
    	      	    || MAX (hex)	AS label
    ,	  MIN (CASE  WHEN hex LIKE '_0'  THEN  hex_bs25000  END)	AS "00"
    ,	  MIN (CASE  WHEN hex LIKE '_1'  THEN  hex_bs25000  END)	AS "01"
    ,	  MIN (CASE  WHEN hex LIKE '_2'  THEN  hex_bs25000  END)	AS "02"
    ,	  MIN (CASE  WHEN hex LIKE '_3'  THEN  hex_bs25000  END)	AS "03"
    ,	  MIN (CASE  WHEN hex LIKE '_4'  THEN  hex_bs25000  END)	AS "04"
    ,	  '...'
    ,	  MIN (CASE  WHEN hex LIKE '_F'  THEN  hex_bs25000  END)	AS "0F"
    FROM	  unpivoted
    GROUP BY  r_num
    ORDER BY  r_num;
    Notice that the sub-query unpivoted is basically your original query, omitting the columns not needed here, and adding some formatting.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,745 Red Diamond
    Answer ✓
    Hi, Mette,

    Displaying one column from several rows as several columns on one row is called pivoting.
    Search for "pivot" for a description and examples.
    In order to pivot, you must have the value to be displayed (hex_bs25000 in your case) on the same row with something that indicates what row and column of the results will receive that value. The last character of hex is good for the column. For the row, you could you the first character of hex, but I find it easier to derrive it from the rn column in your original query.
    COLUMN	"00"	FORMAT A2
    COLUMN	"01"	FORMAT A2
    COLUMN	"02"	FORMAT A2
    COLUMN	"03"	FORMAT A2
    COLUMN	"04"	FORMAT A2
    -- ...
    COLUMN	"0F"	FORMAT A2
    
    WITH	unpivoted	AS
    (
    	SELECT	FLOOR (rn/16)	AS r_num
    	,	LPAD ( upper(substr(dump(tegn,16),instr(dump(tegn,16),':')+2))
    		     , 2
    		     , '0'
    		     )		AS hex
    	,	LPAD ( LTRIM (upper(substr(dump(convert(tegn, 'we8BS2000', 'WE8ISO8859P1'),16),instr(dump(convert(tegn, 'we8BS2000', 'WE8ISO8859P1'),16),':')+1)))
    		     , 2
    		     , '0'
    		     )		AS hex_bs25000
    	FROM	(select level rn, chr(level) tegn, convert(convert(chr(level), 'we8BS2000', 'WE8ISO8859P1'),'WE8ISO8859P1','we8BS2000') convert_back
    from dual connect by level <= 255)
    )
    SELECT	  MIN (hex) || '-'
    	      	    || MAX (hex)	AS label
    ,	  MIN (CASE  WHEN hex LIKE '_0'  THEN  hex_bs25000  END)	AS "00"
    ,	  MIN (CASE  WHEN hex LIKE '_1'  THEN  hex_bs25000  END)	AS "01"
    ,	  MIN (CASE  WHEN hex LIKE '_2'  THEN  hex_bs25000  END)	AS "02"
    ,	  MIN (CASE  WHEN hex LIKE '_3'  THEN  hex_bs25000  END)	AS "03"
    ,	  MIN (CASE  WHEN hex LIKE '_4'  THEN  hex_bs25000  END)	AS "04"
    ,	  '...'
    ,	  MIN (CASE  WHEN hex LIKE '_F'  THEN  hex_bs25000  END)	AS "0F"
    FROM	  unpivoted
    GROUP BY  r_num
    ORDER BY  r_num;
    Notice that the sub-query unpivoted is basically your original query, omitting the columns not needed here, and adding some formatting.
  • 531871
    531871 Member Posts: 315
    Thank you very much for this ...

    regards
    Mette
This discussion has been closed.