CODEWITHSUNDEEP
sqlpivotteradatatranspose
Kevin
Kevin

Reputation: 59

Terdata: Combining multiple fields into Single Row with multiple Colums

I wish to achieve the following transformation in teradata, Combining multiple rows into a single row and converting values in the third column into separate columns. Please see the image given in the link. I have the following constraint the seq_id column is scalable.

Data:

ticket_num     seq_id    err_cde    
---------------------------------
AKOZ01         a         23     
AKOZ02         a         51     
AKOZ03         a         48     
AKOZ04         a         7      
AKOZ05         a         10     
AKOZ06         a         20     
AKOZ07         b         51     
AKOZ08         b         51     
AKOZ10         b         48     
AKOZ11         b         20     
AKOZ12         b         5      
AKOZ13         c         48     
AKOZ14         c         23     
AKOZ15         c         7      
AKOZ16         c         10     
AKOZ17         c         10     
AKOZ18         c         7      
AKOZ19         d         20     
AKOZ20         d         10     
AKOZ21         d         7      
AKOZ22         d         7      
AKOZ23         d         48     
AKOZ24         d         10     
AKOZ25         d         51

Result:

seq_id    err_cde1    err_cde2    err_cde3    err_cde4    err_cde5    err_cde6    err_cde7      
-------------------------------------------------------------------------------------------
a     23          51          48           7            10           20           0     
b     51          51          48           20           5            0            0     
c     48          23          7            10           10           7            0     
d     20          10          7            7            48           10           51

Sample Data

Upvotes: 0

Views: 858

Answers (1)

dnoeth
dnoeth

Reputation: 60462

This is the Standard SQL approach, assign a row number based on the ticket_num and then do MAX(CASE):

SELECT
   seq_id,
   MAX(CASE WHEN rn = 1 THEN err_cde ELSE 0 end) AS err_cde1,
   MAX(CASE WHEN rn = 2 THEN err_cde else 0 end) AS err_cde2,
   MAX(CASE WHEN rn = 3 THEN err_cde else 0 end) AS err_cde3,
   MAX(CASE WHEN rn = 4 THEN err_cde else 0 end) AS err_cde4,
   MAX(CASE WHEN rn = 5 THEN err_cde else 0 end) AS err_cde5,
   MAX(CASE WHEN rn = 6 THEN err_cde else 0 end) AS err_cde6,
   MAX(CASE WHEN rn = 7 THEN err_cde ELSE 0 end) AS err_cde7
FROM
 (
   SELECT
      seq_id,
      err_cde,
      ROW_NUMBER()
      OVER (PARTITION BY seq_id
            ORDER BY ticket_num) AS rn
   FROM tab
 ) AS dt
GROUP BY seq_id

Upvotes: 1

Related Questions