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
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
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
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?
