multiple columns to single column (normalization)

  • Hi, I'm working on normalizing a small part of our DB, but having trouble building a particular query that would enable to move data to an appropriate table.

    Say, i have this table:

    CREATE TABLE [#cars](

    [id] [int] NOT NULL,

    [part1] [int] NULL,

    [part2] [int] NULL,

    [part3] [int] NULL

    ) ON [PRIMARY]

    GO

    INSERT INTO [#cars] (id, [part1], [part2],[part3])

    SELECT 1,1,1,1 UNION ALL

    SELECT 2,0,0,0 UNION ALL

    SELECT 3,0,1,0 UNION ALL

    SELECT 4,1,0,0 UNION ALL

    SELECT 5,0,0,1

    SELECT * FROM #cars

    DROP TABLE #cars

    I want the select result to be:

    carid | carpart

    1 | 1

    1 | 2

    1 | 3

    3 | 2

    4 | 2

    5 | 3

    Thanks for your time.

  • ah, solved it with lots of unions... sometimes it helps to write it in text...

    ill post the query in a sec, if someone else needs it.

    UPDATE: here's the query.

    SELECT id as carid, '1' as carparts FROM #cars WHERE part1=1

    UNION

    SELECT id as carid, '2' as carparts FROM #cars WHERE part2=1

    UNION

    SELECT id as carid, '3' as carparts FROM #cars WHERE part3=1

  • This was removed by the editor as SPAM

  • what about this:

    select id, a.PartId

    from #cars

    cross apply (VALUES (1*[part1]), (2*[part2]), (3*[part3])) a(PartId)

    where a.PartId > 0

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • +1.

    select id,1*part1 from #cars where part1 > 0

    union

    select id,2*part2 from #cars where part2 > 0

    union

    select id,3*part3 from #cars where part3 > 0

    karthik

  • I don't think your table is normalized. Your have two entities, cars and parts. One car can have several parts, on part can be used in several cars. That's an m:n-relationship. This has to be realized by two 1:n-relationships.

    CREATE TABLE #cars(

    [id] [int] NOT NULL primary key( id )

    ) ON [PRIMARY]

    create table #parts(

    Id int not null

    primary key (id )

    )

    create table #mnPartsCars(

    CarId int not null,

    PartId int not null

    );

    insert into #cars

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5

    insert into #parts( Id ) values ( 1 ), ( 2 ), ( 3 );

    insert into #mnPartsCars( CarId, PartId ) values

    ( 1, 1 ),

    ( 1, 2 ),

    ( 1, 3 ),

    ( 3, 2),

    ( 4, 1),

    ( 5, 3);

    select

    CarId = c.id,

    PartId = p.id

    from

    #cars c inner join

    #mnPartsCars mn on

    mn.CarId = c.id inner join

    #parts p on

    p.Id = mn.PartId

    drop table #cars

    drop table #parts

    drop table #mnPartsCars

    There would be two foreign keys on the table #mnPartsCars, one referencing the #Cars-table, one referencing the #Parts-table.

  • I don't think your table is normalized. Your have two entities, cars and parts. One car can have several parts, on part can be used in several cars. That's an m:n-relationship. This has to be realized by two 1:n-relationships.

    You're right it wasn't normalized, that why i needed the query to do an insert into the new normalized table.

    My new structure looks like the one you suggested.

  • ahaaaaaaa 🙂 now I understood

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply