Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 22 Jan 2004 @ 11:43:19 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Pivoting a single attribute (normalizing)
 
From:   Dieter Noeth

  My question deals with normalizing an attribute by pivoting a single row full of data into a more normalized multiple rows containing a single value.  



You'll need a helper table with consecutive numbers up to the maximum length of county_code in it.

I usually create it this way:

CREATE volatile TABLE vt_nums ( n INT)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt_nums VALUES(0);
INSERT INTO vt_nums VALUES(1);
INSERT INTO vt_nums VALUES(2);
INSERT INTO vt_nums VALUES(3);
INSERT INTO vt_nums VALUES(4);
INSERT INTO vt_nums VALUES(5);
INSERT INTO vt_nums VALUES(6);
INSERT INTO vt_nums VALUES(7);
INSERT INTO vt_nums VALUES(8);
INSERT INTO vt_nums VALUES(9);

CREATE TABLE nums(n INT NOT NULL PRIMARY KEY);

INSERT INTO nums
  SELECT
    i1.n +
    i2.n * 10 +
    i3.n * 100 +
    i4.n * 1000
  FROM
    vt_nums i1, vt_nums i2, vt_nums i3, vt_nums i4
;

DROP TABLE vt_nums;

COLLECT STATISTICS ON nums COLUMN n;

(You could also use sys_calendar.calendar.day_of_calendar instead)

Then it's a cross join:

SELECT
  state_code,
  SUBSTRING(county_code
    FROM n
    FOR  (POSITION(',' IN SUBSTRING(county_code || ',' FROM n))) - 1
   ) AS word
FROM tab s,
  (SELECT n FROM nums WHERE n BETWEEN 1 AND 100) tmp
WHERE
  (n <= CHAR_LENGTH(county_code) AND SUBSTRING(county_code FROM n - 1 FOR 1) = ',')
OR
  n = 1
ORDER BY state_code, n;

Dieter



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016