In the cases where a column doesn't apply to a category at all, just return a dummy value containing the empty string - that way all four queries will have...

You've lost me. In that field activation table you posted, there are the same columns per category but with different activation states - which would work, but then you talk...

OK, so it's categorised based on the source of data then. In which case you're basically already there. Each of your queries is going to end up with 30 columns...

If I'm reading that right, it's applying the Category 1 rules to everything in the table EXTR_MILIMETRA, is that correct? So the categorisation doesn't happen on a row-by-row basis, but...

I think the only detail unclear here is quite how the category ID relates to the data in your three tables. Is that something specified in a column in one...

I recommend a pub at this point, but don't worry, it's a solvable problem.

Yup, will totally will work with what you have. Have a look into my example and I'll give you a more tailored example when I'm not on my phone in...

The key bit is how PIVOT works. So this code like this:

Select
 Category,
 Col1,
 Col2,
 Col3,
 Col4
From #FieldLookup
Pivot

If they need to return a different number of columns, they need to be completely different SELECT statements. You could do something like this in a stored procedure:

Alternatively:

Select Category, col1,col2,col3,col4,'' col5,'' col6,'' col7 From #temp Where Category = 1
Union All Select Category, '' col1,'' col2,'' col3,col4,col5,col6,col7 From #temp Where Category = 2

Something like:
select
 Category,
-- Included in category
 IIF(Category In (1), '1', '') As Col1,
 IIF(Category In (4,2), '2', '') As Col2,
 IIF(Category In (2), '3',...