This is a standard recursive common table expression. Here's an article showing the solution: https://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/

WITH cteEmpH(EmployeeID, ManagerID, usrName, lvl)
AS (
 SELECT ID, ManagerID, usrName, 1 AS lvl
 FROM Employee
 WHERE...

• This reply was modified 7 years ago by pietlinden.

Use a common table expression.

https://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/

Mockery? Everybody was a noob at one point. I've been fighthing with SQL Server for about 10 years and I still ask silly questions.

No, without the data engine installed on...

I would leave the tables separate. You can write a query/function in DAX to do the counts. If they're separate, it makes creating basic measures much easier.

LineNo is a reserved word in T-SQL. don't use it as a column name.

This looks like it works...

CREATE TABLE #Test(
 col1 int,
 col2 int,
 col3 int);
GO
INSERT INTO #Test VALUES 
 (1, 203, 1232)
,(2, null, 2343)
,(3, 502, 15566)
,(4, null, 4566)
,(5, null, 4545)
,(6, 601, 4545)
,(7, 601,...

Forum Etiquette: How to post data/code on a forum to get the best help

Job security.

Can't you just right-click on the table and ...Script Table As... Insert/Update/Delete?

have you looked at windowing functions? RANK()? DENSE_RANK()?

So you just slap a CREATE VIEW on the front and list the columns you want to return. Only catch is that you can't use ORDER BY in a view.

This appears to work... but my eyesight is terrible, so test it:

SELECT Party 
 , EIDs = STUFF((
         ...

• This reply was modified 7 years ago by pietlinden. Reason: wrong code!

If you're doing this in PowerBI, don't you want a measure that calculates a count, like COUNTROWS('FactTable') and then use CALCULATE() to modify the evaluation context?

Here's an example of doing...

Okay, this one really works... Maybe my brain is taking the weekend off...

First things first, we need some data (CREATE TABLE and INSERT scripts)

use tempdb;
GO
CREATE TABLE #Sales...

• This reply was modified 7 years, 1 months ago by pietlinden. Reason: figured out how to do the pivot
• This reply was modified 7 years, 1 months ago by pietlinden.
• This reply was modified 7 years, 1 months ago by pietlinden.

OUTER APPLY

Like this?

Note: if you provide readily consumable data (create table and insert scripts), you're far more likely to get a tested answer.

-- STEP 1: set up some...

• This reply was modified 7 years, 1 months ago by pietlinden. Reason: reformat... minor stupid errors