question

Ty Andreasen avatar image
Ty Andreasen asked

table transformation into self referencing from CSV data

I have a table that looks like this

                    
Col1,Col2,Col3                    
A,   NULL,NULL                    
A,   B,   NULL                    
A,   B,   C                    

I want to translate this into a self-referencing table: something like...

                    
ID,     ParentID                     
A,      NULL                    
B,      A                    
C,      B                    
...                    

How can I achieve this? I have a few ideas, but is there a fast way? I have a lot of data to convert from the above format. THe problem isn't getting the CSV data in, it is transforming it into something more useable.
Thank you, T. Andreasen

sql-server-2005csv
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image Squirrel 1 commented ·
can you explain what is COL1, 2, 3 ? how is it transforming to the required table ?
0 Likes 0 ·
Ty Andreasen avatar image Ty Andreasen commented ·
those columns are varchar(50) and the transformation would convert basically to use ID / ParentID as GUIDs and a 'Text' Field would be the representation of the character.
0 Likes 0 ·
Ty Andreasen avatar image Ty Andreasen commented ·
varchar(50) to be exact (where A, B, C, etc. would populate)
0 Likes 0 ·
Kristen avatar image
Kristen answered
            
SELECT *            
INTO #TEMP            
FROM            
(            
SELECT [Col1] = 'A', [Col2] = NULL, [Col3] = NULL            
UNION ALL            
SELECT 'A', 'B', NULL            
UNION ALL            
SELECT 'A', 'B', 'C'            
) AS X            
            
SELECT  [ID] = COALESCE([Col3], [Col2], [Col1]),             
    [ParentID] = CASE WHEN  [Col3] IS NOT NULL THEN [Col2]            
    		WHEN  [Col2] IS NOT NULL THEN [Col1]            
    		ELSE NULL            
    		END            
FROM    #TEMP            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image
Squirrel 1 answered
declare @tbl table            
(            
    Col1	char,            
    Col2	char,            
    Col3	char            
)            
insert into @tbl            
select  'A', NULL, NULL	union all            
select  'A', 'B', NULL	union all            
select  'A', 'B', 'C'            
            
select  ID = Col1, ParentID = NULL            
from    @tbl            
where   Col2	is null            
and Col3	is null            
            
union            
            
select  ID = Col1, ParentID = Col2            
from    @tbl            
where   Col2	is not null            
            
union            
            
select  ID = Col2, ParentID = Col3            
from    @tbl            
where   Col3	is not null            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ty Andreasen avatar image
Ty Andreasen answered

I guess my next question would then be: how can I assign unique GUIDS to all of the ID's that I generate, and use parent assignment in this same manner?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.