Author |
Topic |
asm
Posting Yak Master
140 Posts |
Posted - 2006-02-22 : 03:47:38
|
HI EVERYBODYYOU ALL REQUESTED TO HELP ME TO COMEOUT FROM PROBLEM : I HAVE A TABLE NAME PLUCKIN WHICH DATA STORE: MONYEAR EMPCODE PDATE(ddmmyyy) JOBCODE HCD kg 022006 A01608P 01/02/2006 LA N 0 022006 A01608P 02/02/2006 LA N 0 022006 A01608P 03/02/2006 LA N 0 022006 A01608P 04/02/2006 LA N 0 022006 A01608P 05/02/2006 LH N 0 022006 A01693P 01/02/2006 PL N 21 022006 A01693P 02/02/2006 PL N 25 022006 A01693P 03/02/2006 PL N 15 022006 A01693P 04/02/2006 PL N 25 022006 A01693P 05/02/2006 LH N 0BUT I WANT REPORT LIKE : 022006 A01608P LA N LA N LA N LA N LH N 022006 A01693P 21 N 25 N 15 N 25 N LH N (IF JOBCODE = PL THAN INSTEAD OF JOBCODE KGPLUCK IS SHOWN..)Pls guide how to write the SQL to achive the report...ThanksASM---sample data insertUSE NORTHWINDCREATE TABLE PLUCK (MONYEAR CHAR(6), EMPCODE CHAR(7), PDATE SMALLDATETIME,JOBCODE CHAR(2), HCD CHAR(1), KGPLUCK INT) INSERT INTO PLUCK VALUES('022006', 'A01608P','01/02/2006','LA','N',0);INSERT INTO PLUCK VALUES('022006', 'A01608P','02/02/2006','LA','N',0);INSERT INTO PLUCK VALUES('022006', 'A01608P','03/02/2006','LA','N',0);INSERT INTO PLUCK VALUES('022006', 'A01608P','04/02/2006','LA','N',0);INSERT INTO PLUCK VALUES('022006', 'A01608P','05/02/2006','LH','N',0);INSERT INTO PLUCK VALUES('022006', 'A01693P','01/02/2006','PL','N',21);INSERT INTO PLUCK VALUES('022006', 'A01693P','02/02/2006','PL','N',25);INSERT INTO PLUCK VALUES('022006', 'A01693P','03/02/2006','PL','N',15);INSERT INTO PLUCK VALUES('022006', 'A01693P','04/02/2006','PL','N',25);INSERT INTO PLUCK VALUES('022006', 'A01693P','05/02/2006','LH','N',0); |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
neeraj_sharma
Starting Member
15 Posts |
Posted - 2013-06-12 : 06:14:24
|
CREATE TABLE PLUCK (MONYEAR CHAR(6), EMPCODE CHAR(7), PDATE SMALLDATETIME,JOBCODE CHAR(2), HCD CHAR(1), KGPLUCK INT) INSERT INTO PLUCK VALUES('022006', 'A01608P','01/02/2006','LA','N',0);INSERT INTO PLUCK VALUES('022006', 'A01608P','02/02/2006','LA','N',0);INSERT INTO PLUCK VALUES('022006', 'A01608P','03/02/2006','LA','N',0);INSERT INTO PLUCK VALUES('022006', 'A01608P','04/02/2006','LA','N',0);INSERT INTO PLUCK VALUES('022006', 'A01608P','05/02/2006','LH','N',0);INSERT INTO PLUCK VALUES('022006', 'A01693P','01/02/2006','PL','N',21);INSERT INTO PLUCK VALUES('022006', 'A01693P','02/02/2006','PL','N',25);INSERT INTO PLUCK VALUES('022006', 'A01693P','03/02/2006','PL','N',15);INSERT INTO PLUCK VALUES('022006', 'A01693P','04/02/2006','PL','N',25);INSERT INTO PLUCK VALUES('022006', 'A01693P','05/02/2006','LH','N',0);Select MONYEAR , EmpCode,( Select JobCode +' ' +HCD+' ' From PLUCK T2Where T2.MONYEAR = T1.MONYEAR And T2.EmpCode=T1.EmpCode FOR XML PATH( '' ) )From PLUCK T1 Where EmpCode = 'A01608P' Group By MONYEAR , EmpCode Union ALL Select MONYEAR , EmpCode,( Select Convert ( varchar,KGPLUCK) +' ' +HCD +' ' From PLUCK T2Where T2.MONYEAR = T1.MONYEAR And T2.EmpCode=T1.EmpCode FOR XML PATH( '' ) )From PLUCK T1 Where EmpCode = 'A01693P' Group By MONYEAR , EmpCode www.tutorialsqlserver.com[url][/url]@nrj |
|
|
|
|
|