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, 1 months 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, 2 months ago by pietlinden. Reason: reformat... minor stupid errors

Got some sample data? That would help a LOT.

Looks like this works. Here's some data... this is what Jeff is talking about... readily consumable data (CREATE TABLE and INSERT SCRIPTS.)

use tempdb;
go
-- set up your table
CREATE...

• This reply was modified 7 years, 2 months ago by pietlinden.
• This reply was modified 7 years, 2 months ago by pietlinden.

Try utteraccess.com

In spite of the name, they discuss all MS Office stuff there.

Does 'GetAllFavoriteItems' exist in your current restored database? Sounds like it can't find it.