| Author |
 Topic  |
|
|
kond.mohan
Posting Yak Master
India
154 Posts |
 Posted - 06/04/2012 : 07:32:41
|
A0230 C C 1,2,3,5 Team Work
A0230 C C 1,2,3,5 Communication
A0230 G K 5 Performs assigned tasks with
A0230 G K 5 Punctuality, Leave and Discipline
I AM EXECUTE MY QUERY WITH CASE LOGIC LIKE
SELECT DISTINCT
A.EMP_STAFFID "Employee Code",
CASE WHEN O.TRANS_GOAL_COMP_TYPE='C' THEN o.TRANS_GOAL_COMP_NAME else NULL END C,
CASE WHEN O.TRANS_GOAL_COMP_TYPE='G' THEN o.TRANS_GOAL_COMP_NAME else NULL END G,
MY OUT PUT IS 6 RECORDS ==================
COLUMN1 COLUMN2 COLUMN3
------ ----- -----
A0126 K 5 Punctuality, Leave and Discipline
A0126 K 5 Quality and process improvement
A0126 Kl 5 Word Compare Feedback Saving for
A0126 C 1,2,3,5 Implementa
a0126 C 1,2,3,5 Integrity
a0126 C 1,2,3,5 Processess
MY EXPECTED OUT PUT IS SHOULD GET 3 RECORDS ONLY
COLUMN1 COLUMN2 COLUMN3
---------- ------- ------
A0126 , C 1,2,3,5 Implementa , K 5 Punctuality, Leave and Disciplin
A0126 , C 1,2,3,5 Integrity , K 5 Quality and process improvement
A0126 , C 1,2,3,5 , Kl 5 Word Compare Feedback Saving
ANYBODY KNOW PLS EXPLAIN THE WAY
A0126 C 1,2,3,5 Communication
|
Edited by - kond.mohan on 06/07/2012 02:34:43
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47960 Posts |
Posted - 06/04/2012 : 10:26:29
|
you've not shown us full code. the reason you get 6 rows is not beacuse of CASE....WHEN
I guess its because of involved join with one to many relationship
Show some sample data with join statement and we will be able to help you out
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
kond.mohan
Posting Yak Master
India
154 Posts |
Posted - 06/05/2012 : 00:42:48
|
I AM SHARING THE COMPLETE QUERY
SELECT DISTINCT
A.EMP_STAFFID "Employee Code",
--O.TRANS_GOAL_COMP_TYPE,
NT.TRANS_GOAL_COMP_NAME C,
NT1.TRANS_GOAL_COMP_NAME G,
--CASE WHEN O.TRANS_GOAL_COMP_TYPE='C' THEN o.TRANS_GOAL_COMP_NAME else '' END C,
--CASE WHEN O.TRANS_GOAL_COMP_TYPE='G' THEN o.TRANS_GOAL_COMP_NAME else ''END G,
--O.TRANS_GOAL_COMP_WEIGHTAGE,
A.EMP_FIRSTNAME + ' '+ isnull(A.EMP_MIDDLENAME,'') + ' '+ isnull(A.EMP_LASTNAME,'') "Employee Name",
H.GRADE_NAME "Grade",
I.DESIGNATION_NAME "Designation",
K.SHIFT_NAME AS "Base Shift",
M.MASTER3_NAME as "Function",
L.OU_NAME AS "Project",
E.DM_DEPT_NAME as "Department",
G.LOCATION_NAME "Location",
A.EMP_DATEOFJOINING "Date of Joining",
A.EMP_FUNCTIONALREPORTINGTO "Department Head"
FROM ERM_EMPLOYEE_MASTER A inner JOIN ERM_DEPT_MAST_T E ON E.DM_DEPT_ID=A.EMP_DEPT_ID
inner join ERM_LOCATION_MASTER G ON G.LOCATION_ID=A.EMP_LOCATION_ID
inner JOIN ERM_GRADE_MASTER H ON A.EMP_GRADE_CODE=H.GRADE_CODE
INNER JOIN ERM_DESIGNATION_MASTER I ON I.DESIGNATION_CODE=A.EMP_DESIGNATION_CODE
INNER JOIN MP_SHIFT_MASTER K ON K.SHIFT_CODE=A.EMP_SHIFTCODE
INNER JOIN ERM_OU_MASTER L ON A.OU_ID=L.OU_ID
INNER JOIN ERC_RLG_MASTER3 M ON A.MASTER3_ID=M.MASTER3_ID
INNER JOIN (SELECT DISTINCT PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_STAFFID ,PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_NAME,PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_TYPE
FROM PMS_NEW_EMP_TRANS_DETAILS_T WHERE PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_TYPE='C') NT ON NT.TRANS_STAFFID=A.EMP_STAFFID
INNER JOIN (SELECT DISTINCT PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_STAFFID ,PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_NAME,PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_TYPE
FROM PMS_NEW_EMP_TRANS_DETAILS_T WHERE PMS_NEW_EMP_TRANS_DETAILS_T.TRANS_GOAL_COMP_TYPE='G') NT1 ON NT1.TRANS_STAFFID=A.EMP_STAFFID
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47960 Posts |
Posted - 06/05/2012 : 10:30:15
|
no point in giving query alone. Without seeing some sample data we cant understand how tables are related and whether its one to many etc. Please post some sample data and explain how you want output.
see below for guidelines on posting data
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
kond.mohan
Posting Yak Master
India
154 Posts |
Posted - 06/07/2012 : 02:32:43
|
column1 column2 column3
m1263 , null , c1,c2,c3 dlfs
m1263 , null , c1,c2,c3,c4,sdlfsla
m1263 , null , c1,c2,c3,sdf
m1263 , k1,k2,k3df, null
m1263 , k1,k2,k3fd , null
m1263 , k1,k2,k3dsf, null
my expected out put is
above mentioned 6 ROWS should present in 3 ROWS ONLY
BECAUSE COLUMN2 AND COLUMN3 ARE ABOVE MENTIONED CASE LCGICS USED IN THE QUERY
|
Edited by - kond.mohan on 06/07/2012 02:33:59 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47960 Posts |
|
| |
Topic |
|
|
|
SSMS 2008 QUERY LOGIC
