Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-01-2011
05:35 PM

09-01-2011
05:35 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-06-2011
10:11 AM

09-06-2011
10:11 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-06-2011
10:19 AM

09-06-2011
10:19 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-07-2011
09:31 PM

09-07-2011
09:31 PM

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.

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.