table transformation into self referencing from CSV data

Question

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

Answer 1

0

            
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

answered Nov 26 '09 at 05:05 AM

Kristen ♦
2.2k ● 6 ● 7 ● 10

(comments are locked)

Answer 2

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

Answer 3

0

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?

answered Nov 26 '09 at 12:15 PM

Ty Andreasen
3 ● 1 ● 2 ● 2

Please start a new question or update your original question.

Jan 21 '10 at 10:09 PM graz ♦

(comments are locked)

table transformation into self referencing from CSV data

Follow this question

Related Questions