normalizing stuff

/*all of this is assuming you are using manually created guids for (most) primary keys*/

/*denormalized table to represent your table*/
declare @shit table ([Thing] varchar(1), [Type] varchar(10), [Details] varchar(10), [MoarDetails] varchar(10), [MultiRowDetails] varchar(10));

/*insert some shit*/
insert @shit
    values
        ('a', 'small', 'wtf', 'wasdtwa', 'w111111')
        , ('b', 'small', 'sucka', 'sewewe', 's111111')
        , ('b', 'small', 'sucka', 'sewewe', 's222222')
        , ('c', 'big', 'punk', 'poioisa', 'p111111')
        , ('d', 'big', 'gucci', 'gang', 'g1111111');

select * from @shit;

/*create a table to hold your new guid and some identifying value from the original*/
declare @shitid table ([ID] uniqueidentifier, [Thing] varchar(1));

/*insert some new shit, creating a guid per unique [Thing]*/
insert @shitid
    select newid(), [Thing]
    from @shit
    group by [Thing];

select * from @shitid


/*new normalized tables*/
declare @type table ([ID] uniqueidentifier, [Value] varchar(10));
declare @thing table ([ID] uniqueidentifier primary key, [Thing] varchar(1), [TypeID] uniqueidentifier, [Details] varchar(10));
declare @thing2 table ([ThingID] uniqueidentifier primary key, [MoarDetails] varchar(10)); /*primary key is foreign key to @thing*/
declare @thingmulti table ([ID] int identity primary key, [ThingID] uniqueidentifier, [MultiRowDetails] varchar(10)); /*identity as primary key to prevent fuckery*/
declare @thingmultiALT table ([ThingID] uniqueidentifier, [Order] int, [MultiRowDetails] varchar(10)); /*primary key is foreign key and order*/

/*create lookup shit*/
insert @type
    select newid(), [Type]
    from @shit
    group by [Type];

select * from @type;

/*create parent table*/
insert @thing
    select a.[ID], b.[Thing], c.[ID], b.[Details]
    from @shitid a
    inner join @shit b on a.[Thing] = b.[Thing]
    inner join @type c on b.[Type] = c.[Value]
    group by a.[ID], b.[Thing], c.[ID], b.[Details];

select * from @thing;

/*create 1:1 child table*/
insert @thing2
    select a.[ID], b.[MoarDetails]
    from @shitid a
    inner join @shit b on a.[Thing] = b.[Thing]
    group by a.[ID], b.[MoarDetails];

select * from @thing2;

/*create 1:M child table*/
insert @thingmulti
    select a.[ID], b.[MultiRowDetails]
    from @shitid a
    inner join @shit b on a.[Thing] = b.[Thing]
    group by a.[ID], b.[MultiRowDetails];

select * from @thingmulti;

/*create 1:M child table with order*/
insert @thingmultiALT
    select a.[ID], row_number() over(partition by a.[ID] order by b.[MultiRowDetails]), b.[MultiRowDetails]
    from @shitid a
    inner join @shit b on a.[Thing] = b.[Thing]
    group by a.[ID], b.[MultiRowDetails];

select * from @thingmultiALT;

/*join it back together*/
select a.[ID], a.[Thing], d.[Value] [Type], a.[Details], b.[MoarDetails], c.[MultiRowDetails]
from @thing a
inner join @thing2 b on a.[ID] = b.[ThingID]
inner join @thingmulti c on a.[ID] = c.[ThingID]
inner join @type d on a.[TypeID] = d.[ID]
order by a.[Thing];