SQL Pivot with values

[Farook M]

Hi,

I need Header from User table

Group Name and Sub group from MenuMain table

Under subgroup like add or edit from MenuSub table

GroupSub table value is true then 1 else 0


create table #User (Username_ varchar(50),UserID_ varchar(20))
create table #MenuMain (ID_ varchar(20),Group_ varchar(50),Name_ varchar(50))
create table #MenuSub (ID_ varchar(20),Options_ varchar(50))
create table #GroupSub (UserID_ varchar(20),ID_ varchar(20),Options_ varchar(50))

insert into #User(Username_,UserID_) values ('USER1','101'),('USER2','102'),('USER3','103')
insert into #MenuMain(ID_,Group_,Name_) values ('1001','File','Login'),('1002','File','Logout')
,('1004','Purchase','Indent'),('1005','Purchase','Lpo')
,('1007','Sales','Qtn'),('1008','Sales','SO')
insert into #MenuSub(ID_,Options_) values ('1001','Add'),('1001','Edit'),('1002','Add'),('1002','Edit')
,('1003','Add'),('1003','Edit'),('1004','Add'),('1004','Edit')
,('1005','Add'),('1005','Edit')
,('1007','Add'),('1007','Edit'),('1008','Add'),('1008','Edit')
insert into #GroupSub(UserID_,ID_,Options_) values ('101','1001','Add'),('101','1001','Edit'),
('101','1002','Add'),('101','1002','Edit'),('101','1008','Add'),('101','1008','Edit')
,('102','1001','Add'),('102','1001','Edit'),('102','1002','Add'),('102','1002','Edit')
,('102','1004','Add'),('102','1004','Edit'),('102','1005','Add'),('102','1005','Edit'),
('102','1007','Add'),('102','1007','Edit'), ('102','1008','Add'),('102','1008','Edit')


select * from #User
select * from #MenuMain
select * from #MenuSub
select * from #GroupSub

drop table #User,#MenuMain,#MenuSub,#GroupSub


I wnat result like below screen shot.




Continue reading...