SQL Server dynamic distinct value to column-CodePudding

Let's say I have 2 tables, campaign and mission, where each mission is linked to only one campaign.

Then for each campaign, I can define many extra attributes, each attribute is linked to only one campaign.

For example, campaign1 will have an extra attribute attr1, campaign2 will have another one but with same name attr1, and campaign3 has a different attribute attr3.

For each mission, I can add an extra value for each extra attribute of her campaign.

When I retrieve the data, I want in column the missionId but also the maximum of campaign attribute based on my query (one if there is a filter on one campaign, many if there are more). So the campaignAttr label in column that contain the missionAttr value in row.

I read a bit about pivot but I'm not sure it can help here... Not even sure this is possible at all, so if someone knows how to achieve this I would be very graceful :)

Sample of structure and data:

I want to achieve this to be able to display this table in frontend and let the user sort and filter by the campaign extra attributes.

CodePudding user response：

Does this help:

SELECT MissionID, CampaignId, Attr1, Attr2, Attr3
FROM
    (SELECT MissionID, CampaignId, Label, Value FROM MissionAttr m
     JOIN CampaignAttr c ON m.CampaignAttrid = c.id) AS Source
PIVOT(
      MAX(Value)
      for Label IN (Attr1, Attr2, Attr3)
)AS pivottable;

db fiddle