Remember that CTEs will be available only for one statement and not the whole query/procedure.

Think of them as some kind of re-arranged subqueries.

This will generate the dynamic pivot for any number of years.

Read more about it in the following article: http://www.sqlservercentral.com/articles/Crosstab/65048/

DECLARE @Years nvarchar(4000) = '',

@Amt1 nvarchar(4000)...

And where are you using table2?

You didn't include the CTE definitions and there's a lot of missing information.

Please include the whole code to understand the problem.

You...

There's an alternative you can read about in here:

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

Or maybe something like this:

CREATE TABLE Employee(

EMPID int,

FNAME varchar(100),

LNAME varchar(100))

CREATE TABLE Contact(

...

This is just a suggestion and keep in mind that I don't have many details, but I'd usually work with something like this:

CREATE TABLE Portfolio(

PortFolioID ...

This is an option. I'm not sure about it. I wonder if there's a better option with this structure. Would it be possible to make changes to the table?

DECLARE @PortfolioID...

Sean Lange (6/3/2014)

---

Luis Cazares (6/3/2014)

---

That's an uncommon hierarchy structure. Usually the children have the reference to the parent and not the other way around (that reduces rows from the table).

Could...

I'm sure the problem is with the:

That's an uncommon hierarchy structure. Usually the children have the reference to the parent and not the other way around (that reduces rows from the table).

Could you give an example...

patrickmcginnis59 10839 (6/3/2014)

---

Rudyx - the Doctor (6/3/2014)

---

A great list discussion - my 3 cents:

- table variables need to be depracated with extreme prejudice

- SSMS generated code (IMAX screen...

I noted the same thing when I was testing the code and mention it in my original post. I thought that the sample data might be wrong and posted the...

You're right. Private emails (or any form of communication) is better when dealing with this. People will react better than when you call them out in public and there are...

I'm surprised that Jeff didn't mention that recursive CTEs that count are a form of hidden RBAR (and he usually mentions it even with such few rows).

Here you have...