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

thanks for the head start... I was curious, so I finished it.

 SELECT nm.item,
 Name = NU.Item 
  FROM test T
  CROSS APPLY SCRIDB.dbo.DelimitedSplit8K(T.name, ';') NM
  CROSS APPLY SCRIDB.dbo.DelimitedSplit8K(T.numbers,...

You need to help you. Read the articles Chris posted. Create some tables, try out the different options - LEFT JOIN, EXCEPT, NOT IN.

I'd probably send a tweet to @psdbatools and see what happens. or #psdbatools

Maybe this will help?

https://social.technet.microsoft.com/wiki/contents/articles/1073.robocopy-and-a-few-examples.aspx

There's not enough data to test your query, but this is the general pattern and should work:

use tempdb;
GO
CREATE TABLE #ToGive (
 GiverID INT,
 NumGifts TINYINT,
 GiverName VARCHAR(10),
 City VARCHAR(15)
 );
GO
INSERT INTO #ToGive...

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

So what do you really want to do?

Modify the subscription so it changes when the report is delivered? If so, see the "Schedule" section of this article:

https://docs.microsoft.com/en-us/sql/reporting-services/lesson-3-defining-a-data-driven-subscription?view=sql-server-2017