Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

multiple columns to single column (normalization) Expand / Collapse
Author
Message
Posted Thursday, November 22, 2012 2:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 6:26 AM
Points: 99, Visits: 402
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.
Post #1387776
Posted Thursday, November 22, 2012 2:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 6:26 AM
Points: 99, Visits: 402
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

Post #1387782
Posted Thursday, November 22, 2012 4:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 3,966, Visits: 5,206
How about this:
CREATE TABLE [#cars](
[id] [int] NOT NULL,
[part1] [int] NULL,
[part2] [int] NULL,
[part3] [int] NULL
) ON [PRIMARY]

INSERT [#cars] (id, [part1], [part2],[part3])
VALUES(1,1,1,1),(2,0,0,0),(3,0,1,0),(4,1,0,0),(5,0,0,1)

SELECT * FROM #cars

SELECT id, part1 as Part, 1 as Seq
FROM #cars
WHERE part1 <>0
UNION ALL
SELECT id, part2 as Part, 2 as Seq
FROM #cars
WHERE part2 <>0
UNION ALL
SELECT id, part3 as Part, 3 as Seq
FROM #cars
WHERE part3 <>0

DROP TABLE #cars



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1387833
Posted Thursday, November 22, 2012 4:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:50 AM
Points: 2,856, Visits: 5,124
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1387842
Posted Thursday, November 22, 2012 10:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
+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
Post #1388024
Posted Monday, November 26, 2012 6:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:21 AM
Points: 197, Visits: 730
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.
Post #1388576
Posted Monday, November 26, 2012 6:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 6:26 AM
Points: 99, Visits: 402
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.

Post #1388579
Posted Monday, November 26, 2012 6:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:21 AM
Points: 197, Visits: 730
ahaaaaaaa now I understood
Post #1388594
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse