Table query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table query

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2011-05-09 : 15:35:19

I have two tables named Users and Users_Permissions. Users table fields are UserId (int), Username (varchar(50))
and Users_Permissions table fields are Id (int), UserId (int), Sub_Header_Id (int), Permission (varchar(20)).

User table:


CREATE TABLE [dbo].[Users](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Users_Permission table:


CREATE TABLE [dbo].[Users_Permissions](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NULL,
	[Sub_Header_Id] [int] NULL,
	[Permission] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_Permissions_Permission]  DEFAULT ('No')
) ON [PRIMARY]

Then I wrote this query:


select u.id as Id, u.username as username, p.sub_header_id, p.permission as perm from Users u  LEFT JOIN Users_Permissions p on p.sub_header_id= 18   order by u.Id

and I got the query result like
Id---Username--sub_header_Id--Permission
16------Admin--------17-----------Add
16------Admin--------17-----------Edit
16------Admin--------17-----------Delete
16------Admin--------17-----------View
17------shaji--------15-----------Add
18------jb-----------15-----------View

here I need this to be like this:
Id---Username---sub_header_Id---Perm1---Perm1---Perm3---Perm4
16------Admin------17----------------Add------Edit-----Delete----View
17------shaji-------15----------------Add------NULL-----NULL-----NULL
18------jb---------15-----------------NULL-----NULL-----NULL----View

Can any body help me

Shaji

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-09 : 15:39:25

Have you tried PIVOT?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2011-05-09 : 15:40:23

No idea about pivot table

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-09 : 20:52:53

The syntax is something like this:


select 
   *
from
   YourTable
Pivot
( max(permission) for permission in ([Add],[Edit],[Delete],[View]))P

There is documentation and examples here: http://msdn.microsoft.com/en-us/library/ms177410.aspx The thing about the built-in pivot operator however, is that it is not dynamic. You have to know what columns you want in your pivoted table in advance.

If you don't have that information, you will need to use dynamic pivoting. Madhivanan's blog here has a stored proc that is simple to use that will do dynamic pivoting: http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources