Data Pivoting in Teradata

Database

Data Pivoting in Teradata

I have a scenario where one row in source is transformed into multiple rows in target.

In source i have about 25 columns.

In target i have about 10 columns.

In target there is a 'KEY' column where it is populated based on the values of 14 columns in source.

The logic behind populating that Key coulumn is as follows

Case when src.col1 <> 0 then key =1

         when src.col2 <>0 then key = 2

.

.

.

       when src.col14 <>0 then key =14

The complexity here is in a single record in source col1 <>0 & col2<>0 ....col14 <>0 values may exist. ( I mean in a single record col1, col2....col14 may have values other than zero). In that scenario i should create new row in target . If all 14 source columns are having values other than zero then that single row in source should become 14 rows in target.

Can anyone please help me.

Thanks in advance.

3 REPLIES

Re: Data Pivoting in Teradata

Hi Rahul,

Please see if the below solution works for you. I have taken a table example where I taken a table with 8 attributes COL1...COL8 and first 5 columns are used to create the KEY column in the Target table.

COL1    COL2    COL3    COL4    COL5    COL6    COL7    COL8

--------------------------------------------------------------------

1    2    3    4    5    A    B    C

0    0    0    0    0    D    E    F

1    2    0    0    0    G    H    J

SELECT 1 AS KEY , COL6 , COL7 , COL8

FROM

TABLE

WHERE COL1 <> 0

UNION ALL

SELECT 2 AS KEY , COL6 , COL7 , COL8

FROM

TABLE

WHERE COL2 <> 0

UNION ALL

SELECT 3 AS KEY , COL6 , COL7 , COL8

FROM

TABLE

WHERE COL3 <> 0

UNION ALL

SELECT 4 AS KEY , COL6 , COL7 , COL8

FROM

TABLE

WHERE COL4 <> 0

UNION ALL

SELECT 5 AS KEY , COL6 , COL7 , COL8

FROM

TABLE

WHERE COL5 <> 0

Sample Query Flow

-----------------

1    A    B    C

1    G    H    J

UNION ALL

2    A    B    C

2    G    H    J

UNION ALL

3    A    B    C

UNION ALL

4    A    B    C

UNION ALL

5    A    B    C

Output Result

-------------

KEY    COL6    COL7    COL8

----------------------------

1    A    B    C

1    G    H    J

2    A    B    C

2    G    H    J

3    A    B    C

4    A    B    C

5    A    B    C

Thanks

Manik

Re: Data Pivoting in Teradata

Please Try the below query as well as its a more efficient way to convert columns to rows as it spools the data only once whereas the above query would spool it for 5 times and 14 times in your case. Don't have a database right now to test the query but I hope it works.

Please don't be surprised to see a PRODUCT JOIN between the TABLE and DERIVED TABLE as this technique needs it !!!!

Atleast we know some positive sides of a PRODUCT JOIN :)

SELECT

CASE

WHEN TEMP.DAY = 1 AND COL1 <> 0 THEN 1 ELSE 0

WHEN TEMP.DAY = 2 AND COL2 <> 0 THEN 2 ELSE 0

WHEN TEMP.DAY = 3 AND COL3 <> 0 THEN 3 ELSE 0

WHEN TEMP.DAY = 4 AND COL4 <> 0 THEN 4 ELSE 0

WHEN TEMP.DAY = 5 AND COL5 <> 0 THEN 5 ELSE 0

END AS KEY,

COL6,

COL7,

COL8

FROM

TABLE,

(SELECT DAY

FROM SYS_CALENDAR

WHERE YEAR = 2011

AND MONTH = 1

DAY BETWEEN 1 AND 5

) AS TEMP

WHERE KEY <> 0

Re: Data Pivoting in Teradata

I did Union All Query , But our team members didn't accept that.

Solution given by mjasrotia looks good and it worked out. I created a temp table where i a have one column with 14 records. I did a cross join and applied case logic for KEY and i got it.

Thanks for your valuable suggestions.

I appreciate for your replies.