User master table:
Userid Username Firstname and other informations

Options master table:
Optionid Optionname

UserOptions table:
Useroptionid  Userid  Optionid

I want detail information for all users and all options in my query with user information as below.

------------------------------------------------
user details....  all options...
------------------------------------------------
so user info...    options goes here... like yes/no  yes/no

Below is my query but I want both way information from user and options details so can show on columns using pivot.

select u.*, o.optionname
from users u
		left join useroptions uo on u.userid = uo.userid
		left join options o on o.optionid = uo.optionid

SELECT Username,Firstname,
MAX(CASE WHEN Optionname ='Option1' THEN 'Yes' ELSE 'No' END) AS Option1,
MAX(CASE WHEN Optionname ='Option2' THEN 'Yes' ELSE 'No' END) AS Option2,
MAX(CASE WHEN Optionname ='Option3' THEN 'Yes' ELSE 'No' END) AS Option3,
........
FROM Table
GROUP BY Username, Firstname

exec dynamic_pivot 'select u.*, o.optionid from users u
		left join useroptions uo on u.userid = uo.userid
		full outer join options o on o.optionid = uo.optionid', 'optionname', 'count(optionid)'

uid    uname    fname   lname   phone  opt1   opt2    opt3    opt4    opt5    opt6
NULL	NULL	NULL	NULL	NULL	1	1	0	0	0	1
1	user1	f user1	l user1	3333	0	0	0	1	1	0
2	user2	f user2	l user2	58885	0	0	1	1	1	0
3	user3	f user3	l user3	8844	0	0	0	0	0	0
4	user4	f user4	l user4	8884	0	0	0	0	0	0
5	user5	f user5	l user5	99	0	0	0	0	0	0

quote:

---

Originally posted by visakh16

i think its because of full outer join. whats the need of full join here? can you explain

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by keyursoni85

Hi Visakh,

Yes, I wrote that because I need all options list for each users in columns and all users weather user assigned to that group or any group is assinged to any user or not.

So all users and all options. Due to which I used full outer join.
If I use left outer join then not all options will come in column list of pivot.

quote:

---

Originally posted by visakh16

i think its because of full outer join. whats the need of full join here? can you explain

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

---

quote:

---

Originally posted by keyursoni85

Hi Visakh,

Thanks for your inputs and suggestions.

---

EXEC dynamic_pivot 'select u.*, o.optionid from users u
		cross join useroptions uo 
		left outer join options o on o.optionid = uo.optionid', 'optionname', 'count(optionid)'

EXEC dynamic_pivot 'select  m.*,uo.required columns here...
                   from (select u.userid, o.optionid from users u
		cross join options o )m
		left outer join useroptions uo  on m.optionid = uo.optionid and m.userid = uo.userid', 'optionname', 'count(optionid)'

select * from 
    (
        
		select  m.*, uo.useroptionid
                   from (select optionname as pivot_col, u.userid, o.optionid from users u
		cross join options o )m
		left outer join useroptions uo  on m.optionid = uo.optionid and m.userid = uo.userid

    ) as t 
    pivot 
    (
        count(optionid) for pivot_col in ([Agricultural],[Designer],[Government],[Medical],[Technology],[Test])
    ) as p
       


1	NULL	1	1	1	0	0	1
2	NULL	1	1	0	0	0	1
3	NULL	1	1	1	1	1	1
4	NULL	1	1	1	1	1	1
5	NULL	1	1	1	1	1	1
1	1	0	0	0	1	0	0
1	2	0	0	0	0	1	0
2	3	0	0	0	1	0	0
2	4	0	0	0	0	1	0
2	5	0	0	1	0	0	0

User table..
----------------
userid	username
1	user1
2	user2
3	user3
4	user4
5	user5

Options table..
----------------
optionid	optionname
1	      Agri
2	      Designer
3	      Govt
4	      Medical
5	      Tech
6	      Test

useroption table..
-----------------------
useroptionid	userid	optionid
1	        1	   1
2	        1	   2
3	        2	   1
4	        2	   3

Table insert scripts..
INSERT INTO [dbo].[users]([userid], [username])
SELECT 1, N'user1' UNION ALL
SELECT 2, N'user2' UNION ALL
SELECT 3, N'user3' UNION ALL
SELECT 4, N'user4' UNION ALL
SELECT 5, N'user5'

INSERT INTO [dbo].[options]([optionid], [optionname])
SELECT 1, N'Agri' UNION ALL
SELECT 2, N'Designer' UNION ALL
SELECT 3, N'Govt' UNION ALL
SELECT 4, N'Medical' UNION ALL
SELECT 5, N'Tech' UNION ALL
SELECT 6, N'Test'

INSERT INTO [dbo].[useroptions]([useroptionid], [userid], [optionid])
SELECT 1, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 3, 2, 1 UNION ALL
SELECT 4, 2, 3

select * from 
    (
        
		select  m.userid,m.pivot_col, uo.useroptionid
                   from (select optionname as pivot_col, u.userid, o.optionid from users u
		cross join options o )m
		left outer join useroptions uo  on m.optionid = uo.optionid and m.userid = uo.userid

    ) as t 
    pivot 
    (
        count(optionid) for pivot_col in ([Agricultural],[Designer],[Government],[Medical],[Technology],[Test])
    ) as p
       

SELECT * 
FROM 
	(
	    
		SELECT  m.userid,m.pivot_col, uo.useroptionid
				   FROM (SELECT optionname AS pivot_col, u.userid, o.optionid FROM users u
		CROSS JOIN OPTIONS o )m
		LEFT OUTER JOIN useroptions uo  ON m.optionid = uo.optionid AND m.userid = uo.userid

	) AS t 
	PIVOT 
	(
		COUNT(useroptionid) FOR pivot_col IN ([Agri],[Designer],[Govt],[Medical],[Tech],[Test])
	) AS p