June 4, 2012 at 6:17 am
Dear all
i have a table like this structure
column1 column2 column3
--------- --------- --------
A0230 CC 1,2,3,5 Team Work
A0230 CC 1,2,3,5 Communication
A0230 GK 5 Performs assigned tasks with accuracy
A0230 GK 5 Punctuality, Leave and Discipline
i have prepared query i got this output
SELECT DISTINCT
A.EMP_STAFFID "Employee Code",
CASE WHEN O.TRANS_GOAL_COMP_TYPE='C' THEN o.TRANS_GOAL_COMP_NAME END C,
CASE WHEN O.TRANS_GOAL_COMP_TYPE='G' THEN o.TRANS_GOAL_COMP_NAME END G
from table name
out put is
column1 column3 column4
A0126 Comp 1,2,3,5 Qa null
A0126 Comp 1,2,3,5 Qa null
A0126 null K 5 Achieves indi
A0126 null K 5 Attendance of
my expected out put is like this way
column1 column3 column4
A0126 Comp 1,2,3,5 Qa K 5 Achieves indi
A0126 Comp 1,2,3,5 Qa K 5 Attendance
can any body know the logic pls help me
June 4, 2012 at 6:26 am
Your Sample Data is very unclear. I am not able to make out which data belongs to which column. Please post some better sample data.
Have a look at the link in my signature to know how to post sample data.
June 4, 2012 at 10:50 pm
hi vijayan
this is my 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
June 5, 2012 at 12:23 am
I am not asking about the query. Please post the sample data that you posted in your first post in a more understandable format. I am not able to make out which fields have what data. Please format the sample data and post it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply