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.
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 2013164-----Mukundan--------Java-2----------Q2 2013164-----Mukundan--------Java-3----------Q2 2013164-----Mukundan--------DotNet-1--------Q1 2013164-----Mukundan--------DotNet-2--------Q1 2013164-----Mukundan--------DotNet-3--------Q1 2013164-----Mukundan--------SQL-1-----------Q3 2013164-----Mukundan--------SQL-2-----------Q3 2013164-----Mukundan--------C#-1------------Q4 2013164-----Mukundan--------C#-2------------Q4 2013164-----Mukundan--------C#-3------------Q4 2013164-----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#-1164-----Mukundan--------DotNet-2---Java-2---SQL-2--C#-2164-----Mukundan--------Dotnet-3---Java-3---XXX----C#-3164-----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 ASSELECT 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 ) pvtPIVOT (MAX(TrainingName) FOR PlannedQuarter IN ([Q1 2013], [Q2 2013], [Q3 2013], [Q4 2013]))p[/code]--Chandu |
|
|
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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Bikram panjikar-- Create date: 24 Jan 2013-- Description: To get training details quarterly wise.-- =============================================CREATE PROCEDURE USP_GetQuarterlyTrainingReportASBEGIN -- 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 @TempUSERSELECT DISTINCT EmpID FROM [DBO].[Emptraining]/* Get the total user count */DECLARE @COUNT AS INTSELECT @COUNT = COUNT (*) FROM @TempUSERDECLARE @J AS INTSET @I=1 /* In while loop take the user data one by one from EmpTraining Table and store in Result set */WHILE @I<=@COUNTBEGIN 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 @RESULTSETENDGOB Panjikar |
|
|
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 @EmployeeTrainingSELECT 164, 'Mukundan', 'Java-1', 'Q2 2013' union allSELECT 164, 'Mukundan', 'Java-2', 'Q2 2013' union allSELECT 164, 'Mukundan', 'Java-3', 'Q2 2013' union allSELECT 164, 'Mukundan', 'DotNet-1', 'Q1 2013' union allSELECT 164, 'Mukundan', 'DotNet-2', 'Q1 2013' union allSELECT 164, 'Mukundan', 'DotNet-3', 'Q1 2013' union allSELECT 164, 'Mukundan', 'SQL-1', 'Q3 2013' union allSELECT 164, 'Mukundan', 'SQL-2', 'Q3 2013' union allSELECT 164, 'Mukundan', 'C#-1', 'Q4 2013' union allSELECT 164, 'Mukundan', 'C#-2', 'Q4 2013' union allSELECT 164, 'Mukundan', 'C#-3', 'Q4 2013' union allSELECT 164, 'Mukundan', 'C#-4', 'Q4 2013' union allSELECT 104, 'Mukundan', 'SQL-1', 'Q3 2013' union allSELECT 104, 'Mukundan', 'SQL-2', 'Q3 2013' union allSELECT 104, 'Mukundan', 'C#-1', 'Q4 2013' union allSELECT 104, 'Mukundan', 'C#-2', 'Q4 2013' union allSELECT 104, 'Mukundan', 'C#-3', 'Q4 2013' union allSELECT 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 ) pvtPIVOT (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 |
|
|
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 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|
|
|
|
|