T SQL - Split and merge columns

Question

Hi

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

Answer 1

What do you have on each row in that one column?

Answer 2

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.

Answer 3

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.

Answer 4

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.