Extracting Data from Badly Designed Table

I've inherited a crappy data structure on a project, see table below
...
CREATE TABLE [REPL_COMPONENT]
(
[ReplacementID] [int] IDENTITY (1, 1) NOT NULL ,
[RepairID] [int] NULL ,
[Repl1] [int] NULL ,
[ReplCost1] [int] NULL ,
[ReplType1] [int] NULL ,
[Repl2] [int] NULL ,
[ReplCost2] [int] NULL ,
[ReplType2] [int] NULL ,
[Repl3] [int] NULL ,
[ReplCost3] [int] NULL ,
[ReplType3] [int] NULL ,
[Repl4] [int] NULL ,
[ReplCost4] [int] NULL ,
[ReplType4] [int] NULL
)
I'd like to write a query to extract this data in normal form. So, for
each row in this table, my query would return 4 rows.
Given, this one row of dummy data:
INSERT INTO [REPL_COMPONENT]([RepairID], [Repl1], [ReplCost1],
[ReplType1], [Repl2], [ReplCost2], [ReplType2], [Repl3], [ReplCost3],
[ReplType3], [Repl4], [ReplCost4], [ReplType4])
VALUES(53, 11, 11, 11, 22, 22, 22, 33, 33, 33, 44, 44, 44)
The new query would return 4 rows
1 53 11 11 11
1 53 22 22 22
1 53 33 33 33
1 53 44 44 44
Appreciate your help!Simple:
select ReplacementID, RepairID, Repl1, ReplCost1, ReplType1 from
REPL_COMPONENT
union all
select ReplacementID, RepairID, Repl2, ReplCost2, ReplType2 from
REPL_COMPONENT
union all
select ReplacementID, RepairID, Repl3, ReplCost3, ReplType3 from
REPL_COMPONENT
union all
select ReplacementID, RepairID, Repl4, ReplCost4, ReplType4 from
REPL_COMPONENT
*mike hodgson*
http://sqlnerd.blogspot.com
George wrote:

>I've inherited a crappy data structure on a project, see table below
>...
>CREATE TABLE [REPL_COMPONENT]
> (
>[ReplacementID] [int] IDENTITY (1, 1) NOT NULL ,
>[RepairID] [int] NULL ,
>[Repl1] [int] NULL ,
>[ReplCost1] [int] NULL ,
>[ReplType1] [int] NULL ,
>[Repl2] [int] NULL ,
>[ReplCost2] [int] NULL ,
>[ReplType2] [int] NULL ,
>[Repl3] [int] NULL ,
>[ReplCost3] [int] NULL ,
>[ReplType3] [int] NULL ,
>[Repl4] [int] NULL ,
>[ReplCost4] [int] NULL ,
>[ReplType4] [int] NULL
> )
>I'd like to write a query to extract this data in normal form. So, for
>each row in this table, my query would return 4 rows.
>Given, this one row of dummy data:
>INSERT INTO [REPL_COMPONENT]([RepairID], [Repl1], [ReplCost1],
>[ReplType1], [Repl2], [ReplCost2], [ReplType2], [Repl3], [ReplCost3],
>[ReplType3], [Repl4], [ReplCost4], [ReplType4])
>VALUES(53, 11, 11, 11, 22, 22, 22, 33, 33, 33, 44, 44, 44)
>The new query would return 4 rows
>1 53 11 11 11
>1 53 22 22 22
>1 53 33 33 33
>1 53 44 44 44
>Appreciate your help!
>
>|||George,
rather than try to work round a 'crappy' data structure, why not simply
re-invent it and normalize the table at an early stage. Log-tern benifits
usually outweigh the short-term effort.
Tony
"George" wrote:

> I've inherited a crappy data structure on a project, see table below
> ...
> CREATE TABLE [REPL_COMPONENT]
> (
> [ReplacementID] [int] IDENTITY (1, 1) NOT NULL ,
> [RepairID] [int] NULL ,
> [Repl1] [int] NULL ,
> [ReplCost1] [int] NULL ,
> [ReplType1] [int] NULL ,
> [Repl2] [int] NULL ,
> [ReplCost2] [int] NULL ,
> [ReplType2] [int] NULL ,
> [Repl3] [int] NULL ,
> [ReplCost3] [int] NULL ,
> [ReplType3] [int] NULL ,
> [Repl4] [int] NULL ,
> [ReplCost4] [int] NULL ,
> [ReplType4] [int] NULL
> )
> I'd like to write a query to extract this data in normal form. So, for
> each row in this table, my query would return 4 rows.
> Given, this one row of dummy data:
> INSERT INTO [REPL_COMPONENT]([RepairID], [Repl1], [ReplCost1],
> [ReplType1], [Repl2], [ReplCost2], [ReplType2], [Repl3], [ReplCost3],
> [ReplType3], [Repl4], [ReplCost4], [ReplType4])
> VALUES(53, 11, 11, 11, 22, 22, 22, 33, 33, 33, 44, 44, 44)
> The new query would return 4 rows
> 1 53 11 11 11
> 1 53 22 22 22
> 1 53 33 33 33
> 1 53 44 44 44
> Appreciate your help!
>