My code basically looks for the Causality or Relatedness columns and then counts back from there. That was the only sensible way of doing it. The weird 1/3 or whatever......

just wondering. There's not really a way to achieve this without dynamic SQL, right? I don't think so because you don't know beforehand how many of the repeating groups there...

Your sample desired output looks a lot like a PowerPivot in Excel. So I don't think trying to write a query for this is maybe what you want. (or is...

• This reply was modified 5 months, 1 weeks ago by pietlinden. Reason: more information/clarification
• This reply was modified 5 months, 1 weeks ago by pietlinden.

Okay, I sorted it out. It's not ideal yet... It should be a stored procedure and should receive the tablename containing the data as input. Just because it's usually called...

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