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 6 years, 3 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 6 years, 3 months ago by pietlinden.
• This reply was modified 6 years, 3 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.

Why the DISTINCT?

where ID in

(

select distinct ID from SourceDB.dbo.Table2 where CreateDateTime<@CutOffPeriod)

Doesn't matter if there are duplicates in the list you're matching to. DISTINCT is expensive. Use it only when you...

I got it to work, but I flat out cheated. I used Access (which most everyone here hates), but it was the easiest solution for me.

I created a table in...

You should be able to do this (create calculated columns) inside SSRS... or are you not using SSRS?

Totally forgot about STRING_AGG()

SELECT SUM(Qty) AS Counted 
, Article
, STRING_AGG(Color,', ') AS Colors
FROM Things
GROUP BY Article;

• This reply was modified 6 years, 4 months ago by pietlinden.

I don't think so. I couldn't get the concatenation of Colors to work without FOR XML.

SELECT  SUM(Qty) AS 'Counted'
 , ColorGrp.Article
 , ColorGrp.Colors 
FROM
(SELECT Article
 , STUFF((SELECT ', ' +...

Looks like it should be:

WHERE (TYPE <> 35 AND ACCT1 <> 2)
OR (TYPE IN (30, 31) AND ACCT1 = 6)

unpivot/normalize, filter, repivot?

Thanks Des! (I hope that was a hard query... otherwise, I'm in trouble!)

Problems like this make me really like normalization. =)

After failing at this several times, this is as far as I've gotten. I'd love to see the right answer, because this...

• This reply was modified 6 years, 4 months ago by pietlinden. Reason: forgot to include the link the to the "Many uses of CROSS APPLY" article
• This reply was modified 6 years, 4 months ago by pietlinden.
• This reply was modified 6 years, 4 months ago by pietlinden.

Amen to that! It's a truly awful design. To the OP - performance of queries against a table like this are going to be painfully slow. You should be able...