tsql - I am using SQL Server 2005 facing multiple rows displaying error -

here's 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 required output show data 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       table1 t1 left join     table2 30th_plntm_data on 30th_plntm_data.plno = t1.plno                            , 30th_plntm_data.plndt = '20150430'   left join     table2 01st_plntm_data on 01st_plntm_data.plno = t1.plno                            , 01st_plntm_data.plndt = '20150501' left join     table2 02nd_plntm_data on 02nd_plntm_data.plno = t1.plno                            , 02nd_plntm_data.plndt = '20150502' 

but query not getting above format data...

please have idea?

what you're trying known pivot query, transforming rows columns , question flagged duplicate tell basics of how this, answers there doesn't tell step need take why post answer (even though flagged question duplicate).

as dynamic pivot uses aggregate function max() determine item should value each new column , grouping data mrno, ipno max plntm every date (plndt) need add layer of grouping rows items don't have max value.

to need apply row_number() function source data query in end:

declare @sql nvarchar(max) declare @cols nvarchar(max)  select @cols= isnull(@cols + ',','') + quotename(plndt) (select distinct plndt table2) types  set @sql =   n'select mrno, ipno, plno, ' + @cols + ' (        select            t1.mrno, t1.ipno, t1.plno, t2.plndt, t2.plntm,            rn = row_number() on (              partition t1.mrno, t1.ipno, t1.plno, t2.plndt               order t2.plndt, t2.plntm           )               table1 t1     join         table2 t2 on t2.plno = t1.plno  ) x     pivot(max(plntm)           plndt in (' + @cols + ')) pvttable'  exec sp_executesql @sql