Pivot table Non-unique values

Asked Jan 09 '20 at 21:06

Active Jan 09 '20 at 21:22

Viewed 183 times

I have two tables that I want to join together into 1 row by accessruleId for an ssrs report. The issue is FieldName has dynamic values and the same fieldName could appear more than once It seems like Pivot table is what I want but examples I've looked at is pivoting off a unique value and doing an aggregate function. Below is the output for query 1 and 2 and I want to combine the results so it looks like screenshot 3. If a given fieldname shows up more than once (businessArea in this case) I want to make the values comma delimited. Any links to examples similar to my need would be greatly appreciated. I'm using sql server 2016

Query 1

select ar.AccessRuleId, ar.EffectiveDate, ar.TermDate, ar.CreatedByUser, ar.LastUpdatedUser
from [AccessRule].[AccessRule] ar
where ar.AccessRuleId = 1

Query 2

select rf.FieldName, ro.[Value]
from [AccessRule].[RuleOperation] ro
join [AccessRule].[RuleField] rf on ro.FieldId = rf.RuleFieldId
where ro.AccessRuleId = 1

edited Jan 09 '20 at 21:22

asked Jan 09 '20 at 21:06

user1152145

1

Please provide your queries as text and data as tabular text. We cannot make an efficient use of pictures. – GMB Jan 09 '20 at 21:08
1

Is this similar to the question @ https://stackoverflow.com/q/14783680/461822 – Sai Puli Jan 09 '20 at 21:20
Yes it does! I will take a look. Thanks – user1152145 Jan 09 '20 at 21:25

Pivot table Non-unique values

0 Answers0