I am using SQL Server 2005 facing multiple rows displaying error

Question

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?

possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — jpw, May 12 '15 at 08:36
On a side note, table names can not start with a number unless you use delimited identifiers (that is, enclose the name in double quotation marks (") or brackets ([ ])) so this: `30TH_PLNTM_DATA` would be invalid and needs to be changed to `[30TH_PLNTM_DATA]` or `"30TH_PLNTM_DATA"`. — jpw, May 12 '15 at 10:00

score 0 · Accepted Answer · answered May 12 '15 at 09:01

What you're trying to do is known as a pivot query, transforming rows to columns and the question I flagged as duplicate will tell you the basics of how to do this, but the answers there doesn't completely tell you about the extra step you need to take which is why I post this answer (even though I flagged the question as a duplicate).

As the dynamic pivot uses an aggregate function like max() to determine what item should be the value for each new column you and grouping your data by MRNO, IPNO would get the max PLNTM for every date (PLNDT) you need to add an extra layer of grouping to get the extra rows for the items that don't have the max value.

To do this you need to apply the row_number() function to the source data so the query would look like this in the end:

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @cols AS NVARCHAR(MAX)

SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(PLNDT)
FROM (SELECT DISTINCT PLNDT FROM Table2) AS Types

SET @sql =
  N'SELECT MRNO, IPNO, PLNO, ' + @cols + ' FROM (
       SELECT 
          t1.MRNO, t1.IPNO, t1.PLNO, t2.PLNDT, t2.PLNTM, 
          rn = ROW_NUMBER() OVER (
             PARTITION BY t1.MRNO, t1.IPNO, t1.PLNO, t2.PLNDT 
             ORDER BY t2.PLNDT, t2.PLNTM
          )
    FROM   
       TABLE1 T1
    JOIN 
       TABLE2 t2 ON t2.PLNO = T1.PLNO 
) X
    PIVOT(MAX(plntm)
          FOR plndt IN (' + @cols + ')) AS PVTTable'

EXEC sp_executesql @sql

I am using SQL Server 2005 facing multiple rows displaying error

1 Answers1