Author |
Topic |
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-09-27 : 09:47:18
|
Hi,I am confused about pivot query.User master table:Userid Username Firstname and other informationsOptions master table:Optionid OptionnameUserOptions table:Useroptionid Userid OptionidI 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/noBelow is my query but I want both way information from user and options details so can show on columns using pivot.select u.*, o.optionnamefrom users u left join useroptions uo on u.userid = uo.userid left join options o on o.optionid = uo.optionid |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-27 : 10:24:35
|
[code]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 TableGROUP BY Username, Firstname[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-09-27 : 10:36:42
|
Hi Visakh,Thanks,But its static. I want to draw dynamic query using pivot with which I am little confused.So in future other options automatically append in columns. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-09-28 : 01:55:54
|
Hi Visakh,Thanks, I did that way but one problem I am facing.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)' I have 5 options out of 5 users. I only used 3 options for first 2 users. So I am getting one extra row NULL for other options.I have used full outer join as I want all information from both tables.Anything I am doing wrong? How can I avoid that NULL row for options.* //Output//uid uname fname lname phone opt1 opt2 opt3 opt4 opt5 opt6NULL NULL NULL NULL NULL 1 1 0 0 0 11 user1 f user1 l user1 3333 0 0 0 1 1 02 user2 f user2 l user2 58885 0 0 1 1 1 03 user3 f user3 l user3 8844 0 0 0 0 0 04 user4 f user4 l user4 8884 0 0 0 0 0 05 user5 f user5 l user5 99 0 0 0 0 0 0 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-28 : 10:35:12
|
i think its because of full outer join. whats the need of full join here? can you explain------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-09-29 : 06:14:42
|
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. |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-10-01 : 01:27:45
|
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 MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-01 : 10:36:26
|
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 MVPhttp://visakhm.blogspot.com/
then you should be using a cross join between users and options to form a matrix tablethen left join from matrix table to your table and do the pivot------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-10-02 : 05:34:17
|
Hi Visakh,Thanks for your inputs and suggestions. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-02 : 10:35:41
|
quote: Originally posted by keyursoni85 Hi Visakh,Thanks for your inputs and suggestions.
were you able to get it working?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-10-03 : 01:28:16
|
No Visakh,It does not gives all options in columns still.I tried below solution.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)' Can you please correct me if I am wrong at any place in join?If I use right outer join then it returns very previous results with one NULL row like full outer join. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 10:32:08
|
i think it should be thisEXEC 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)' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-10-03 : 10:43:52
|
Ok,I tried but still not giving proper results.. It retuns 5 null rows first and then required result after that total 10 rows.below is output and query tried.. can you please give me query with pivot function so can check with it.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 12 NULL 1 1 0 0 0 13 NULL 1 1 1 1 1 14 NULL 1 1 1 1 1 15 NULL 1 1 1 1 1 11 1 0 0 0 1 0 01 2 0 0 0 0 1 02 3 0 0 0 1 0 02 4 0 0 0 0 1 02 5 0 0 1 0 0 0 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 10:47:39
|
shouldnt it be count(useroptionid) ?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-10-03 : 12:22:19
|
Still nothing, If count(useroptionid) then it will return each userwise 6 rows of each options.. of if 5 users and 6 options then 30 rows.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 12:26:42
|
can you show how exactly is data from your table ie a sample of about 10 rows with column headers properly aligned (post within code tags)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-10-03 : 12:40:45
|
[code]User table..----------------userid username1 user12 user23 user34 user45 user5Options table..----------------optionid optionname1 Agri2 Designer3 Govt4 Medical5 Tech6 Testuseroption table..-----------------------useroptionid userid optionid1 1 12 1 23 2 14 2 3Table insert scripts..INSERT INTO [dbo].[users]([userid], [username])SELECT 1, N'user1' UNION ALLSELECT 2, N'user2' UNION ALLSELECT 3, N'user3' UNION ALLSELECT 4, N'user4' UNION ALLSELECT 5, N'user5'INSERT INTO [dbo].[options]([optionid], [optionname])SELECT 1, N'Agri' UNION ALLSELECT 2, N'Designer' UNION ALLSELECT 3, N'Govt' UNION ALLSELECT 4, N'Medical' UNION ALLSELECT 5, N'Tech' UNION ALLSELECT 6, N'Test'INSERT INTO [dbo].[useroptions]([useroptionid], [userid], [optionid])SELECT 1, 1, 1 UNION ALLSELECT 2, 1, 2 UNION ALLSELECT 3, 2, 1 UNION ALLSELECT 4, 2, 3[/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 12:55:01
|
ok. i got the issue. the reason was you included optionid field also inside pivot query. it should be as followsselect * 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 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-10-03 : 13:06:16
|
Yes, Great thanks.Below is final one.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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 13:10:09
|
so is this working as expected?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Previous Page&nsp;
Next Page
|