January 10, 2011 at 1:54 am
Table User
Id Username
1 A
2 B
3 C
4 D
Table Roles
Id UserId Role Status Expiration
1 1 Admin Active 01-01-2011
2 2 Client Active 02-02-2011
3 3 Applicant Active 03-03-2011
4 4 Client Inactive 04-04-2011
Output:
A B C D -- this is the username
Admin Client Applicant Client -- role of the username
Active Active Active inactive -- status of the username
01-01-2011 02-02-2011 03-03-2011 04-04-2011 -- expiration of username
I want to achieved the result above. User and Roles table are having a one to one relationship. Username along with the roles information should be in one column respectively. For example User A having a roles information of Admin, Active and 01-01-2011 came from Role, Status, Expiration fields respectively of table Roles. any help pls on how to achieved this result?
January 10, 2011 at 2:47 am
If I have acknowledged your query properly then I assumed that you looking for result like :-
user id1having roles of ADMIN, and status is ACTIVE since 2011-01-01 00:00:00.000
I have used this query to generate the output:-
select 'user id', a.id ,'having roles of ' , b.role , ', and status is ',b.status,' since ',b.expiration
from users a, roles b
where a.id = b.id.
------------------------
I am considering my solution to your query is very basic and not sure if this is what your requirement is about, but thought to share if it helps.
----------
Ashish
January 14, 2011 at 12:29 pm
Here we can rotate the data by first unpivoting in cte2 and then using pivot in the final select
;WITH cte AS
(
SELECT Id, Username, Role, Status, Expiration
FROM Roles
CROSS APPLY
(SELECT Username FROM [User] WHERE Id = Roles.Id) AS Z
)
,
cte2 AS
(
SELECT Row, Id, Comp
FROM cte
CROSS APPLY
(SELECT 1, Username UNION ALL
SELECT 2, Role UNION ALL
SELECT 3, Status UNION ALL
SELECT 4, CONVERT(VARCHAR(10), Expiration, 120)) AS Z (Row, Comp)
)
SELECT [1], [2], [3], [4]
FROM cte2
PIVOT (MAX(Comp) FOR Id IN ([1], [2], [3], [4])) AS Z
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply