SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help Needed in Tricky Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bpanjikar1985
Starting Member

India
3 Posts

Posted - 01/24/2013 :  00:35:55  Show Profile  Reply with Quote
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
2224 Posts

Posted - 01/24/2013 :  01:41:56  Show Profile  Reply with Quote

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
Go to Top of Page

bpanjikar1985
Starting Member

India
3 Posts

Posted - 01/24/2013 :  03:19:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 01/24/2013 :  03:29:58  Show Profile  Reply with Quote
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
Go to Top of Page

bpanjikar1985
Starting Member

India
3 Posts

Posted - 01/24/2013 :  23:03:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 01/24/2013 :  23:17:56  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000