SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Detail information using pivot
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 09/27/2012 :  09:47:18  Show Profile  Reply with Quote
Hi,

I am confused about pivot query.

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/27/2012 :  10:24:35  Show Profile  Reply with Quote

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


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

Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 09/27/2012 :  10:36:42  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/27/2012 :  10:41:43  Show Profile  Reply with Quote
you can make it dynamic

see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

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

Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 09/28/2012 :  01:55:54  Show Profile  Reply with Quote
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    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

Edited by - keyursoni85 on 09/28/2012 02:13:55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/28/2012 :  10:35:12  Show Profile  Reply with Quote
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/

Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 09/29/2012 :  06:14:42  Show Profile  Reply with Quote
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.
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 10/01/2012 :  01:27:45  Show Profile  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/01/2012 :  10:36:26  Show Profile  Reply with Quote
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/






then you should be using a cross join between users and options to form a matrix table

then left join from matrix table to your table and do the pivot

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

Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 10/02/2012 :  05:34:17  Show Profile  Reply with Quote
Hi Visakh,

Thanks for your inputs and suggestions.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/02/2012 :  10:35:41  Show Profile  Reply with Quote
quote:
Originally posted by keyursoni85

Hi Visakh,

Thanks for your inputs and suggestions.


were you able to get it working?

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

Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 10/03/2012 :  01:28:16  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  10:32:08  Show Profile  Reply with Quote
i think it should be this

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)'




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

Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 10/03/2012 :  10:43:52  Show Profile  Reply with Quote
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 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  10:47:39  Show Profile  Reply with Quote
shouldnt it be count(useroptionid) ?

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

Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 10/03/2012 :  12:22:19  Show Profile  Reply with Quote
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..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  12:26:42  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 10/03/2012 :  12:40:45  Show Profile  Reply with Quote

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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  12:55:01  Show Profile  Reply with Quote
ok. i got the issue. the reason was you included optionid field also inside pivot query. it should be as follows



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
       



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

Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 10/03/2012 :  13:06:16  Show Profile  Reply with Quote
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
	   



Edited by - keyursoni85 on 10/03/2012 13:06:47
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  13:10:09  Show Profile  Reply with Quote
so is this working as expected?

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

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000