mardi 12 mai 2015

I am using SQL Server 2005 facing multiple rows displaying error

Here's my Table1 structure

MRNO   IPNO  PLNO
1      2     1324
2      3     1325
3      4     1326

Table2 structure

MRNO   IPNO  PLNO   PLNDT      PLNTM
1      2     1324   20140430   13:24
1      2     1324   20140430   15:12
1      2     1324   20150501   12:01
1      2     1324   20150501   16:01
1      2     1324   20150501   17:21
1      2     1324   20150502   10:11
1      2     1324   20150502   13:01
1      2     1324   20150502   15:13

Here's my required output I would like to show the data as follows

MRNO    IPNO   30TH_PLNTM_DATA      01ST_PLNTM_DATA  02ND_PLNTM_DATA
1       2       13:24               12:01            10:11
1       2       15:12               16:01            13:01
1       2                           17:21            15:13

SQL code:

SELECT  
   MRNO, IPNO, 
   30TH_PLNTM_DATA.PLNTM,
   01ST_PLNTM_DATA.PLNTM,
   02ND_PLNTM_DATA.PLNTM
FROM   
   TABLE1 T1
LEFT JOIN 
   TABLE2 30TH_PLNTM_DATA ON 30TH_PLNTM_DATA.PLNO = T1.PLNO 
                          AND 30TH_PLNTM_DATA.PLNDT = '20150430'  
LEFT JOIN 
   TABLE2 01ST_PLNTM_DATA ON 01ST_PLNTM_DATA.PLNO = T1.PLNO 
                          AND 01ST_PLNTM_DATA.PLNDT = '20150501'
LEFT JOIN 
   TABLE2 02ND_PLNTM_DATA ON 02ND_PLNTM_DATA.PLNO = T1.PLNO 
                          AND 02ND_PLNTM_DATA.PLNDT = '20150502'

But that query is not getting the above format data..

Please anyone have any idea?

Thanks,

~San

Aucun commentaire:

Enregistrer un commentaire