(Apparently, I like to double-post... and the checker here doesn't work.)

• This reply was modified 2 days, 12 hours ago by pietlinden.
• This reply was modified 2 days, 12 hours ago by pietlinden.

I was thinking of writing an iTVF to basically do the CROSS APPLY stuff for me. I'd do it tonight, but SQL Saturday is tomorrow... so I'll try it this...

The basic pattern is SymptomName, Causality#, Relatedness#

It's oncology stuff, so there are no other column names like that. Causality and Relatedness all have numeric suffixes from 1 to however many...

The basic pattern is SymptomName, Causality#, Relatedness#

It's oncology stuff, so there are no other column names like that. Causality and Relatedness all have numeric suffixes from 1 to however many...

Oh, I STRING_AGG WITHIN GROUP was a good catch. Thanks for that!

The bigger problem is that I have lots of tables with the same hideous design. That's what I was...

Funnily enough, I have that book too. Guess I should be brave and actually open it!

Kinda to answer my own question... I'd definitely recommend reading Star Schema: The Complete Reference before reading Data Warehouse Toolkit. Complete Reference starts from a much less theoretical place, so...

• This reply was modified 3 weeks, 4 days ago by pietlinden.

Oh that! LOL

Thanks Frederico!

Apologies to the OP, rcrock, for sorta hijacking his thread.

Here's the fixed code:

--desc: ugly search scans all string column types for a specific value
IF...

• This reply was modified 3 months ago by pietlinden.
• This reply was modified 3 months ago by pietlinden.

??

I'm confused. I copied and pasted your code and tried to run it. It returns this error:

Msg 156, Level 15, State 1, Procedure sp_uglysearch, Line 84 [Batch Start Line 7]

Incorrect...

This is what I came up with... (btw... with that many points, you can't post CREATE TABLE etc statements for people?)

--CREATE TABLE LogTable (tableName NVARCHAR(50), columnName NVARCHAR(50));
--GO
--...

Sounds like you have serious design issues in your database. I don't envy you one bit.

But you can use a query like this to get all the column names and...

I started reformatting the code... here's my reformat (FWIW):

SELECT
sa.partid AS ALLOC_Part,
sa.quantity,
sa.ordertype,
sa.worksorderid,
sa.reference,
sa.stockvalue,
apm.partdesc,
soi.orderid,
soi.itemnumber,
soi.partid AS SOI_Part,
so.traderid,
t.[name],
u.[name] As SalesRep,
MAX(sub.Max_Batchvalue) AS High_Batchvalue
FROM stockallocations sa
INNER JOIN worksorders wo ON
sa.worksorderid = wo.id
INNER JOIN allpartmaster...

The most obvious thing is that you're using INNER joins everywhere. If you want to keep all the records from one table, you need to OUTER join from that to...

What happened when you tried it?

with only 15 records, I'm not sure you'll see a huge difference, but give it a try and record both times. I didn't write this...

Generally speaking, if you can provide a clear question, some sample data (create table and insert scripts), and an expected output, you can ask all kinds of questions and get...