What version of SQL Server are you using?

If you use a recursive CTE to traverse the tree then you can create the path from the root to specific node and...

Your SELECT statement could return more than one row (group) so it doesn't make to much sense to store the count into a variable because you do not know which...

The trick here is where to start so I used the logic where members are not related to someone (relatedTo is null) or they are related to someone but the...

If there was a mistake in the posting for the sample data and the combination of (userId, BaseYear) identifies uniquely each row then your solution is viable.

We could also use...

Luis,

Based on the sample data, it makes no sense to order by [BaseYear] inside a partition because the combination does not identify uniquely a row so the enumeration will be...

I don't doubt about the parser order but whatever goes beyond that does not have to be in accordance with the parser. A SELECT statement without ORDER BY does not...

SELECT ...[/code]

The row_number function here is not deterministic, meaning the result could change even if it runs against the same data, because the ORDER BY clause is meaningless (ORDER BY NULL).

You...

You can use the xml methods "nodes" and "value" to extract the node in question and either enumerate the rows or use another function against the xml data type.

DECLARE @x...

When you post, there is an IFCode Shortcuts box to the left or from the menu with some helpful tags.

Example:

SELECT 'Hello World!' AS c1;

I guess that the posibility of having at least one deltastore (with rows) per partition after loading data could yield less compression for the partitioned columnstore index.

Try rebuilding the columnstore...

It is well expalined in this blog.

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx

Check the appendix and be sure you are disabling "remote proc transaction promotion" and enabling "rpc out".

Good luck!

I wish you could post table schema and sample data.

SET NOCOUNT ON;

USE tempdb;

GO

WITH C1 AS (

SELECT

T.first_name,

T.last_name,

R.degree,

R.pos,

ROW_NUMBER()OVER(PARTITION BY T.first_name, T.last_name ORDER BY R.pos) AS rn

FROM

(

VALUES

('John', 'Smith', '', 'BA', '', 'MBA'),

('Some',...

Try:

--WITH XMLNAMESPACES (

--'http://www.w3.org/2001/XMLSchema-instance' AS xsi,

--'http://www.w3.org/2001/XMLSchema' AS xsd

--)

SELECT

(

SELECT

'' AS [Description],

T2.RCode AS RCode,

'' AS [Override]

FROM

#TAICR AS T2

WHERE

T2.TAICID = T1.ID

AND RCodes = 'Comment'

ORDER BY

XMLOrder

FOR XML PATH('RCode'), TYPE

) AS Comment,

(

SELECT

'' AS [Description],

T2.RCode AS RCode,

''...

If you need access to the intermediate computation from the previous row then LAG will not do it.

If I recall well, there was a suggestion from Marcello Poletti, with the...