Hi all,
I have the following tables
Tbl_Request
RequestType NoOfPositionsRequired SkillCategory
Req1 10 .Net
Req2 3 Java
Req1 2 SQL
Req3 5 Java
-
Tbl_User
ID SkillCategory Experienced
--
101 Java 0
102 .Net 1
103 Java 1
104 SQL 1
105 .Net 0
106 J2EE 0
Experience is a bool column.
Required Output:
SkillCategory Req1 Req2 Req3 TotalDemand Exp NonExp Total Supply
.Net 12 0 0 12 1 1 2
Java 0 3 5 8 1 2 2
SQL 1 0 0 1 1 0 1
-
Well the first half of it I am able to retrieve meaning the 'Demand' part by pivoting it from the table request and the next part i.e. 'Supply' is also obtained in the similar fashion.
Tbl_User may contain more skill categories than those mentioned in Tbl_Request. So the output should reflect only those categories that are existing in tbl_Request. How can we combine the both? I have taken both the outputs in two temp tables. Now I would like to know if I can combine them and show it as one output or if there is any other better way of doing it.
I am using a stored procedure which is called for my web application so I didn't go for views. Can someone tell me how to do it.
You can combine using join statement
Sample
Code Snippet
TempTable1 - SkillCategory,Req1,Req2,Req3,TotalDemand
TempTable2 - SkillCategory,Exp,NonExp,TotalSupply
Select T1.SkillCategory,T1.Req1,T1.Req2,T1.Req3,T1.TotalDemand,T2.Exp,T2.NonExp,T2.TotalSupply
from TempTable1 T1
left join TempTable2 T2 on T2.SkillCategory = T1.SkillCategory
|||Hi Vidhura,
That solution works fine for my web application.But for using that procedure as dataset for the reporting services I face an error. I can't use two temp tables in a procedure.I want to know if this can be achieved without making use of temporary table
|||Hi,
Using the above SQL Statement just replace the two temptables with select statements, as below
Code Snippet
--create table #Tbl_Request (
--RequestType varchar(20),
--NoOfPositionsRequired int ,
--SkillCategory varchar(20)
--)
--
--insert into #Tbl_Request values ('Req1', 10,'.Net')
--insert into #Tbl_Request values ('Req2',3,'Java')
--insert into #Tbl_Request values ('Req1',2,'SQL')
--insert into #Tbl_Request values ('Req3',5,'Java')
--
--create table #Tbl_User (ID int, SkillCategory varchar(20) ,Experienced int)
--
--insert into #Tbl_User values (101, 'Java', 0)
--insert into #Tbl_User values (102, '.Net', 1)
--insert into #Tbl_User values (103, 'Java', 1)
--insert into #Tbl_User values (104, 'SQL', 1)
--insert into #Tbl_User values (105, '.Net', 0)
--insert into #Tbl_User values (106, 'J2EE', 0)
select
*
from (
SELECT
SkillCategory,
isnull(Req1,0) Req1,
isnull(Req2,0)Req2,
isnull(Req3,0) Req3,
isnull(Req1,0)+ isnull(Req2,0)+ isnull(Req3,0) as TotalDemand
FROM
(select SkillCategory,NoOfPositionsRequired,RequestType from #Tbl_Request) p
PIVOT
(
SUM (NoOfPositionsRequired)
FOR RequestType IN
( Req1, Req2, Req3 )
) AS pvt ) as firsttable
left join
(
SELECT SkillCategory, [0] as Exper,[1] as NonExp
FROM
(select ID, SkillCategory,Experienced from #Tbl_User ) p
PIVOT
(
COUNT (ID)
FOR Experienced IN
( [1], [0] )
) AS pvt
) as secondtable on
firsttable.skillcategory = secondtable.skillcategory
Hope that helps
Matt
|||1 - Your result does not make much sense.
1.1 'J2EE' is not in the final result
1.2 '.Net' appears just one time in table [Tbl_Request] and the value of [NoOfPositionsRequired] is 10. So the value 12 for [Req1] in the final result is not correct
2 - For this kind of problem / question, is very helpful to post DDL, including constraints and indexes, sample data in the form of "insert" statements and expected result. That way we do not have to waste our time simulating your environment. The help should be in both way, shouldn't it?
Code Snippet
-- POST DDL
create table dbo.Tbl_Request (
RequestType varchar(10) not null,
NoOfPositionsRequired int not null,
SkillCategory varchar(15) not null
)
go
create table dbo.Tbl_User (
ID int not null,
SkillCategory varchar(15) not null,
Experienced smallint not null
)
go
-- POST SAMPLE DATA
insert into dbo.Tbl_Request values('Req1', 10, '.Net')
insert into dbo.Tbl_Request values('Req2', 3, 'Java')
insert into dbo.Tbl_Request values('Req1', 2, 'SQL')
insert into dbo.Tbl_Request values('Req3', 5, 'Java')
go
insert into dbo.Tbl_User values(101, 'Java', 0)
insert into dbo.Tbl_User values(102, '.Net', 1)
insert into dbo.Tbl_User values(103, 'Java', 1)
insert into dbo.Tbl_User values(104, 'SQL', 1)
insert into dbo.Tbl_User values(105, '.Net', 0)
insert into dbo.Tbl_User values(106, 'J2EE', 0)
go
declare @.pvt_columns nvarchar(max)
declare @.sel_columns nvarchar(max)
declare @.isnull nvarchar(max)
declare @.sum_col nvarchar(max)
declare @.sql nvarchar(max)
set @.pvt_columns = stuff(
(
select ',' + quotename(RequestType)
from (select distinct RequestType from dbo.Tbl_Request) as t
order by RequestType
for xml path('')
), 1, 1, '')
set @.isnull = N'isnull(' + replace(@.pvt_columns, ',', N',0),isnull(') + N', 0)'
set @.sum_col = replace(@.isnull, ',isnull', '+isnull')
set @.sel_columns = stuff(
(
select ',isnull(' + quotename(RequestType) + ',0) as ' + quotename(RequestType)
from (select distinct RequestType from dbo.Tbl_Request) as t
order by RequestType
for xml path('')
), 1, 1, '')
set @.sql = N'
select
coalesce(a.SkillCategory, b.SkillCategory) as [SkillCategory],
' + @.sel_columns + N',' +
@.sum_col + N'as TotalDemand,
b.Exp,
b.NonExp,
[Total Supply]
from
(
select
*
from
dbo.Tbl_Request
pivot
(
sum(NoOfPositionsRequired)
for RequestType in (' + @.pvt_columns + N')
) as pvt
) as a
full outer join
(
select
SkillCategory,
sum(case when Experienced = 1 then 1 else 0 end) as Exp,
sum(case when Experienced = 0 then 1 else 0 end) as NonExp,
sum(1) as [Total Supply]
from
dbo.Tbl_User
group by
SkillCategory
) as b
on a.SkillCategory = b.SkillCategory
order by
coalesce(a.SkillCategory, b.SkillCategory)
'
exec sp_executesql @.sql
go
drop table dbo.Tbl_User, dbo.Tbl_Request
go
You have to be careful with SQL injection.
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
|||Thank you hunchback..Thanks a lot. Apologies for the lack of schema. I will keep that in mind the next time i post some qustions on the forum.Firstly my view requires me to have only those skills as demanded to appear and not all the skills that are possible. Hence J2EE was not included. Because there are some160 skills that covers all the associates and not all of whom are to be shown i.e not all those skill category people are to be shown. Secondly that was a small mistake and you are right about the count being 10 not 12 for .Net.
So could you tell me how I can achieve this..
Thanks again
|||
Hi,
A simple where clause would do it, in both select statement
Code Snippet
WHERE
SkillCategory IN ('.NET', 'SQL' .... )
Sure you could amend the above procedure to pass a list in as a variable
Code Snippet
declare @.listSkills varchar(100)
set @.ListSkills = '''.NET'',''SQL'''
set @.sql = N'
select
coalesce(a.SkillCategory, b.SkillCategory) as [SkillCategory],
' + @.sel_columns + N',' +
@.sum_col + N'as TotalDemand,
b.Exp,
b.NonExp,
[Total Supply]
from
(
select
*
from
dbo.Tbl_Request
pivot
(
sum(NoOfPositionsRequired)
for RequestType in (' + @.pvt_columns + N')
) as pvt
) as a
where
skillcategory IN ('@.ListSkills')
full outer join
(
select
SkillCategory,
sum(case when Experienced = 1 then 1 else 0 end) as Exp,
sum(case when Experienced = 0 then 1 else 0 end) as NonExp,
sum(1) as [Total Supply]
from
dbo.Tbl_User
where
skillcategory IN ('@.ListSkills')
group by
SkillCategory
) as b
on a.SkillCategory = b.SkillCategory
order by
coalesce(a.SkillCategory, b.SkillCategory)
Hope that helps
Matt
No comments:
Post a Comment