T SQL - Split and merge columns

I've got a problem I've not come across before. I've got an Excel datasource I've loaded into SQL table and I am trying to transform into something a bit more sensible. What I've got is column A in the SQL table from Excel that contains all 3 fields of data but I need to add columns to replace the groups of Owner and Stage

Note a user can have many stages and a stage can have many projects. Project names are unique.

example

My Source Table that I have imported from Excel has 1 column

[ColumnA]
Owner: David Jones 
  Stage1: Suspect 
Project A  
Project B 
 开发者_C百科Stage2:Qualified Suspect
Project C 
Project D  
Owner: John Doe
  Stage1:Suspect
Project E 
  Stage2:Qualified Suspect
Project F 
Project G 

then I want my target rows to look like

[owner]        [stage]                      [project]
David Jones    Stage1:Suspect               ProjectA
David Jones    Stage1:Suspect               ProjectB
David Jones    Stage2:Qualified Suspect     ProjectC
David Jones    Stage1:Qualified Suspect     ProjectD
John Doe       Stage1:Suspect               ProjectE
John Doe       Stage1:Qualified Suspect     ProjectF
John Doe       Stage1:Qualified Suspect     ProjectG

Thanks in advance

G

Table_1 is a SQL Table you would import your Excel into.

    select * from Table_1
declare @temp varchar(50)
declare @owner varchar(50)
declare @stage varchar(50)
Declare My_Cursor cursor 
For
Select ColumnA
from Table_1

Open My_Cursor
Fetch Next from My_Cursor into @temp
while(@@Fetch_Status=0)
begin
if @temp like 'Owner%'
set @owner = @temp
if @temp like 'Stage%'
set @stage=@temp
if @temp like 'Project%'
begin
select LTRIm(SUBSTRing(@owner,7,LEN(@owner))),@stage,@temp
end
Fetch NExt from My_Cursor into @temp
end
close My_Cursor
deallocate My_Cursor

Output:

    ColumnA
--------------------------------------------------
Owner: David Jones
Stage1: Suspect
Project A
Project B
Stage2: Qualified Suspect
Project C
Project D
Owner: John Doe
Stage1: Suspect
Project E
Stage2: Qualified Suspect
Project F
Project G

(13 row(s) affected)


-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
David Jones                                        Stage1: Suspect                                    Project A

(1 row(s) affected)


-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
David Jones                                        Stage1: Suspect                                    Project B

(1 row(s) affected)


-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
David Jones                                        Stage2: Qualified Suspect                          Project C

(1 row(s) affected)


-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
David Jones                                        Stage2: Qualified Suspect                          Project D

(1 row(s) affected)


-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
John Doe                                           Stage1: Suspect                                    Project E

(1 row(s) affected)


-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
John Doe                                           Stage2: Qualified Suspect                          Project F

(1 row(s) affected)


-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
John Doe                                           Stage2: Qualified Suspect                          Project G

(1 row(s) affected)

Replace the select statement with an insert into your table. Let me know if it works.

If I have understood this correctly, the owner and stage of a project are determined by the last owner and stage record(s) read in before the project row.

Which produces a big problem - in most relational databases (including SQLServer), you can't guarantee that the output order of a query matches the order that the rows were entered into the table. In other words, just from querying [ColumnA], there is no way to tell that Project A belongs to David Jones and Stage1:Suspect, rather than (eg.) John Doe and Stage2:Qualified Suspect.

If there isn't already (at the very least) a column holding the Excel row number in the database source table, then such a column should be added and the table should be reloaded.

I think it would be simpler to amend the Excel spreadsheet to derive the owner, stage and project columns, and to load the table from the amended spreadsheet.

Assuming your single column is already available in the database table, one thing I can suggest is for you to write a dynamic sql (Stored procedure). Create a new table with 3 columns(owner, stage, project) say FinalResult.

Then you can get each row from your excel datatable using a cursor and insert the results based on your logic into that new FinalResult table.

Finaly you can retrieve those results from the new table to get desired result.