manssourianm (5/29/2014)

---

Luis,

Thanks for your help.

Changing the datatype to (max) in both places helped. Now I get the correct crosstab columns and rows, but the data part is completely blank.

I had...

That seems as a good approach. I'll leave 2 alternatives which might not be better.

WITH VLeaders AS(

SELECT l.LeaderID,

...

You can add more conditions to your JOIN using AND. I did part of the job here. You need to work on the rest.

SELECT Projects, Hours

FROM Projects

LEFT JOIN ProjTime

...

Did you change both values to nvarchar(max)?

One is in the variable declaration and one is in the query.

You need to use a LEFT JOIN between Projects and ProjTime.

If you put the month condition in the WHERE clause, it will become an INNER JOIN, so change it to...

This is well explained on the following article by Jeff Moden. You should read it (and maybe the first part as well) to fully understand how to do it.

http://www.sqlservercentral.com/articles/Crosstab/65048/

This is...

Try printing your @sql variable to know how the string is formed and identify syntax errors more easily.

Maybe you could use SQL Server Profiler with the correct filters and use the option "Discard results after execution" (Options->Query Results->SQL Server->Results to Grid).

Maybe someone else can give a better...

That might give you a semi cross join which will lead to incorrect results. Date calculations can prevent that but you might get some inconsistencies. Check the following:

CREATE TABLE #table1...

Putting aside the fact that this is a really old thread.

You could prevent the errors using format files and staging tables. That's basically the same rule as using a...

You can use a staging table where you insert the values in 2 rows and then use the code to insert them into the final table with a single row.

How...

I'm not sure if it will work the same way. If r.BankID will always be 26 characters long this should be equivalent and SARGable. If not, there's a risk for...

Alvin Ramard (5/28/2014)

---

ChrisM@Work (5/28/2014)

---

Alvin Ramard (5/28/2014)

---

Luis Cazares (5/27/2014)

---

Alvin,

The code makes clear that there's a concatenation based on multiple values on a single column.

CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck'...

This is why I wanted sample data. That way I could try the code. :hehe:

The problem is that I used the columns wrong in the OVER clause. It should read...

This is just an idea that might work and a shot in the dark at most. Maybe a nested replace will perform better.

Something like this:

DECLARE @CSV varchar(100)...