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
 Help Needed in Tricky Query

Author  Topic 

bpanjikar1985
Starting Member

3 Posts

Posted - 2013-01-24 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-24 : 01:41:56
[code]
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
[/code]

--
Chandu
Go to Top of Page

bpanjikar1985
Starting Member

3 Posts

Posted - 2013-01-24 : 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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-24 : 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
Go to Top of Page

bpanjikar1985
Starting Member

3 Posts

Posted - 2013-01-24 : 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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-24 : 23:17:56
Welcome...

What are the data Values for PlannedQuarter column?
Check the below link for dynamic pivot. It will help you
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Let me know once done



--
Chandu
Go to Top of Page
   

- Advertisement -