| Author |
 Topic  |
|
|
bpanjikar1985
Starting Member
India
3 Posts |
 Posted - 01/24/2013 : 00:35:55
|
I have a table EmployeeTraining and i want to get a view from this able as multiple column from single column.
SampleTable: EmployeeTraining
______________________________________________________
EmpID---EmpName---------TrainingName----PlannedQuarter
______________________________________________________
164-----Mukundan--------Java-1----------Q2 2013
164-----Mukundan--------Java-2----------Q2 2013
164-----Mukundan--------Java-3----------Q2 2013
164-----Mukundan--------DotNet-1--------Q1 2013
164-----Mukundan--------DotNet-2--------Q1 2013
164-----Mukundan--------DotNet-3--------Q1 2013
164-----Mukundan--------SQL-1-----------Q3 2013
164-----Mukundan--------SQL-2-----------Q3 2013
164-----Mukundan--------C#-1------------Q4 2013
164-----Mukundan--------C#-2------------Q4 2013
164-----Mukundan--------C#-3------------Q4 2013
164-----Mukundan--------C#-4------------Q4 2013
__________________________________________________
From above EmployeeTraining i need the view as below table :
_________________________________________________________
EmpID---EmpName---------Q1---------Q2-------Q3-----Q4
_________________________________________________________
164-----Mukundan--------DotNet-1---Java-1---SQL-1--C#-1
164-----Mukundan--------DotNet-2---Java-2---SQL-2--C#-2
164-----Mukundan--------Dotnet-3---Java-3---XXX----C#-3
164-----Mukundan--------XXX--------XXX-----XXX----C#-4
_________________________________________________________
Note: I NEED VIEW OR RESULT AS BELOW TABLE FROM EmployeeTraining Table [No matter what technique you use- cursor, view, temp table etc]
Please help me on the same. thanks in advance.
B Panjikar |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1420 Posts |
Posted - 01/24/2013 : 01:41:56
|
CREATE VIEW View_name
AS
SELECT
EmpID,
EmpName,
COALESCE([Q1 2013], 'xxx') AS Q1,
COALESCE([Q2 2013], 'xxx') AS Q2,
COALESCE([Q3 2013], 'xxx') AS Q3,
COALESCE([Q4 2013], 'xxx') AS Q4
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY PlannedQuarter ORDER BY (SELECT 1)) rn
FROM @EmployeeTraining
) pvt
PIVOT (MAX(TrainingName) FOR PlannedQuarter IN ([Q1 2013], [Q2 2013], [Q3 2013], [Q4 2013]))p
--
Chandu |
 |
|
|
bpanjikar1985
Starting Member
India
3 Posts |
Posted - 01/24/2013 : 03:19:16
|
Hello Chandu,
Thanks a lot for your solution it works for me fine for single user, but i have modified it for multiple user as below:
-- ================================================
/* Stored Procedure to get the training report.*/
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Bikram panjikar
-- Create date: 24 Jan 2013
-- Description: To get training details quarterly wise.
-- =============================================
CREATE PROCEDURE USP_GetQuarterlyTrainingReport
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
/* Declare a temp table for resultSet */
DECLARE @RESULTSET AS TABLE
(
EMPID VARCHAR(50),
EMPNAME VARCHAR(50),
Q1 VARCHAR(50),
Q2 VARCHAR(50),
Q3 VARCHAR(50),
Q4 VARCHAR(50)
);
/* Declare a Temp table to get the all unique EmpId from EmpTraining table*/
DECLARE @TempUSER AS TABLE
(
Idx INT IDENTITY(1,1),
EMPID INT
);
INSERT INTO @TempUSER
SELECT DISTINCT EmpID FROM [DBO].[Emptraining]
/* Get the total user count */
DECLARE @COUNT AS INT
SELECT @COUNT = COUNT (*) FROM @TempUSER
DECLARE @J AS INT
SET @I=1
/* In while loop take the user data one by one from EmpTraining Table and store in Result set */
WHILE @I<=@COUNT
BEGIN
declare @tempuserid as int
select @tempuserid =empid from @TempUSER where Idx=@J
insert into @RESULTSET
select
EmpID,
EmpName,
COALESCE([Q1 2013], 'xxx') AS Q1,
COALESCE([Q2 2013], 'xxx') AS Q2,
COALESCE([Q3 2013], 'xxx') AS Q3,
COALESCE([Q4 2013], 'xxx') AS Q4
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY PlannedQuarter ORDER BY (SELECT 1)) rn
FROM Emptraining where EmpID=@tempuserid) pvt
PIVOT (MAX(TrainingName) FOR PlannedQuarter IN ([Q1 2013], [Q2 2013], [Q3 2013], [Q4 2013]))P
set @I=@I+1 /*Increament I value */
End
/*Get the result set*/
select * from @RESULTSET
END
GO
B Panjikar |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1420 Posts |
Posted - 01/24/2013 : 03:29:58
|
See this one........
No need of that long procedure. simple SELECT statement is enough
DECLARE @EmployeeTraining TABLE(EmpID int, EmpName VARCHAR(15), TrainingName VARCHAR(20), PlannedQuarter VARCHAR(10))
insert into @EmployeeTraining
SELECT 164, 'Mukundan', 'Java-1', 'Q2 2013' union all
SELECT 164, 'Mukundan', 'Java-2', 'Q2 2013' union all
SELECT 164, 'Mukundan', 'Java-3', 'Q2 2013' union all
SELECT 164, 'Mukundan', 'DotNet-1', 'Q1 2013' union all
SELECT 164, 'Mukundan', 'DotNet-2', 'Q1 2013' union all
SELECT 164, 'Mukundan', 'DotNet-3', 'Q1 2013' union all
SELECT 164, 'Mukundan', 'SQL-1', 'Q3 2013' union all
SELECT 164, 'Mukundan', 'SQL-2', 'Q3 2013' union all
SELECT 164, 'Mukundan', 'C#-1', 'Q4 2013' union all
SELECT 164, 'Mukundan', 'C#-2', 'Q4 2013' union all
SELECT 164, 'Mukundan', 'C#-3', 'Q4 2013' union all
SELECT 164, 'Mukundan', 'C#-4', 'Q4 2013' union all
SELECT 104, 'Mukundan', 'SQL-1', 'Q3 2013' union all
SELECT 104, 'Mukundan', 'SQL-2', 'Q3 2013' union all
SELECT 104, 'Mukundan', 'C#-1', 'Q4 2013' union all
SELECT 104, 'Mukundan', 'C#-2', 'Q4 2013' union all
SELECT 104, 'Mukundan', 'C#-3', 'Q4 2013' union all
SELECT 104, 'Mukundan', 'C#-4', 'Q4 2013'
SELECT
EmpID,
EmpName,
COALESCE([Q1 2013], 'xxx') AS Q1,
COALESCE([Q2 2013], 'xxx') AS Q2,
COALESCE([Q3 2013], 'xxx') AS Q3,
COALESCE([Q4 2013], 'xxx') AS Q4
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY empId, PlannedQuarter ORDER BY (SELECT 1)) rn
FROM @EmployeeTraining
) pvt
PIVOT (MAX(TrainingName) FOR PlannedQuarter IN ([Q1 2013], [Q2 2013], [Q3 2013], [Q4 2013]))p
EDIT: Red Marked column names are the values of PlannedQuarter
--
Chandu |
Edited by - bandi on 01/24/2013 23:20:32 |
|
|
|
bpanjikar1985
Starting Member
India
3 Posts |
Posted - 01/24/2013 : 23:03:00
|
Thanks Chandu! your query is working fine for my scenario!!! Stil a small help is needed actually i would like to replace COALESCE([Q1 2013], 'xxx') AS Q1,
"[Q1 2013]" <---- with dynamic variable, but its not working. Any help is appreciated. Thanks a lot.
B Panjikar |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1420 Posts |
|
| |
Topic |
|
|
|
Help Needed in Tricky Query
