## Message Posted: Wed, 07 Jun 2006 @ 16:45:47 GMT

 < Last>>

 Subj: SQL to Normalize Columns to Rows From: Anomy Anom

<-- Anonymously Posted: Wednesday, June 07, 2006 11:30 -->

I have a Brand O database conversion problem where 250 row values were mashed into 250 columns and the table has grown to 13,000,000 rows. Most of the rows in the 250 columns are null. The old table looks like this:

Existing Table (Ugh!)

```     Customer_Number     Integer Not Null PK,
Col001              Decimal(9,2),
Col002              Decimal(9,2),
Col003              Decimal(9,2),
Col004              Decimal(9,2),
Col005              Decimal(9,2),
Col006              Decimal(9,2),
Col007              Decimal(9,2),
.
Col250              Decimal(9,2)
```

New Table

```     Customer_Number     Integer,
Type_ID             SmallInt,
Sales_Amount        Decimal(9,2)
```

We're trying to come up with a set based solution to make this conversion happen as fast as possible. Options we're looking at are:

```     250 Unions Inserted into New_Table - the issue here is 250 x 13 MM =
3.25 billion rows in the set of Unions
Insert into New_Table
Select Customer_Number, 1, Col001 From Old_Table Where Col001 is not
null
Union
Select Customer_Number, 2, Col002 From Old_Table Where Col002 is not
null
Union
.
Select Customer_Number, 250, Col250 From Old_Table Where Col250 is not
null
```

Stored Procedure using Cursor (much code omitted for clarity) - the issue here is anemic cursor performance.

```     Selects * from Old_Table
If Col001 is not null
Insert into New_Table
(Customer_Number, 1, Col001)
End If

If Col002 is not null
Insert into New_Table
(Customer_Number, 1, Col001)
End If

.    .
If Col250 is not null
Insert into New_Table
(Customer_Number, 2, Col250)
End If
```

Do 250 FastExports with 250 FastLoads to a staging table each iteration does an insert to New_Table and clears staging for next. The drawback here is authoring a flood of scripts.

Virtually every Brand O database conversion to Teradata has this issue (I know of one Brand O DW that had 5,000 columns in one table), does anyone have a better solution?

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2006 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback