So I have narrowed down the root cause to the following finding:

select case_desc from table where id = 13245

Please see the screenshot for the cell case_desc in the result, basically...

Findings:

I just noticed one of the row was split into two rows in excel when it is copied, I believe there are more, which messed up the rest.

Jeff,

I am shocked when I woke up and saw you have such a long reply to my question, I think that's why I love this forum, thank you very much...

Thank you Jeff,

I completely agree with you, however you know often the time you will have to follow your client, not asking client to follow you.

It might be possible to...

Thanks, still very tedious, actually adding one step here because data is eventually to be imported into SQL (or NoSQL), right?

Thank you Steve,

Your solution is nice however will sometimes not applicable to some cases, like mine: I am not allowed to create stored procedures or functions on that server....

Sorry, for the later visitor, here is the solution:

DECLARE @test-2 varchar(max);

set @test-2 = 'US01/00000/12345/123456';

set @test-2 = Replace(@test, '/', '.');

SELECT ParseName(@test, 4)

SELECT ParseName(@test, 3)...

sgmunson (12/8/2016)

---

halifaxdal (12/8/2016)

---

I am stuck here today:

I have some excel 2013 files with more than the maximum rows that I cannot convert it to excel 2003/2007 format.

My current office is...

Thank you Steve, the work was submitted already but yes your help would be seen by later visitor.

Y.B. (11/28/2016)

---

halifaxdal (11/25/2016)

---

Thank you very much.

3. Is it possible? Probably but I'm hesitant to give more solutions you won't be able to maintain. I'd recommend you get a feel...

Thank you very much.

Your script use MAX instead of SUM, is it a typo or is there any consideration?

New testing result:

1. The new script (MAX or SUM) returns incorrect stats...

Thank you very much for the revised script which is amazing, the result returns lots of NULL for WeekNo fields, how can the script be modified to get that?

I also...

I finally fixed everything and have result now!!!

The reason it didn't return value is because weekno in two tables does not match.

I fixed the grammer error in my script, but it returns no sum value for each week:crying:

Here is my script:

Declare @sql as varchar(Max)

Declare @Columns as varchar(Max)

Select

@Columns =

COALESCE(@Columns +'],...

I also tried:

WITH PivotData AS (

select [ProjectWork Number], WeekNO, WeekValue From dbo.staging

)

select [ProjectWork Number], WeekValue, [Apr-03-2016], [Apr-10-2016]

From PivotData

PIVOT (

Sum(WeekValue) For...