SQL query

Question

I have records as shown above. need report in this format without showing username multiple times.

1st column are the rights of the user.

reports output should be some thing like below

a)

UserName    Email    Support

Jcampbell      True       True

or please recommend some other presentable report which shows user with his rights

---

Naveen| Press Yes if the post is useful.

Answer 1

Naveen,

From the description it looks like you are looking for creating a report in the format shown above. If yes and you are using SSRS, you need not pivot this using SQL (assuming the user rights list is long) instead a column grouping on User Rights column and a details grouping (row grouping) on username column should be able to give the results you are looking for.

But if you are looking for SQL you can use a query like below

select username,
max(case when UserRights = 'API' then 'True' else 'False' end) as API,
max(case when UserRights = 'Email' then 'True' else 'False' end) as Email,
max(case when UserRights = 'Support' then 'True' else 'False' end) as Support,
-- build similar case statements for all user rights here
max(case when UserRights = 'Dashboard' then 'True' else 'False' end) as Dashboard

from UserRights
group by username

---

--sIbu

Answer 2

I would say doing this in SSRS would be easy

for that use a query as below for your dataset

SELECT u.UserName,r.Rights,CASE WHEN t.UserName IS NOT NULL THEN 1 ELSE 0 END AS RightsExists
FROM
(
SELECT DISTINCT UserName
FROM Table
)u
CROSS JOIN (SELECT DISTINCT Rights
FROM Table
)r
LEFT JOIN Table t
ON t.Rights = r.Rights
AND t.UserName = u.UserName

Then add a simple matrix in report

Add rowgroup as Username

columngroup as Rights

and in data add below expression

=Max(IIF(Fields!RightsExists.Value = 1,"True","False"))

---

Please Mark This As Answer if it solved your issue
Please Mark This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Page
My Personal Blog
My Facebook Page

Answer 3

Naveen,

It it is SSRS, I was referring to this solution in my post above.

Dataset query:

select username, userrights from yourtable

Row Group: username

Column Group: userrights

value expression: =iif(CountRows() = 0, False, True)

SSRS output would be as shown in the snapshot below.

---

--sIbu