Skip to content
Advertisement

Without using aggregate function can we do pivot in sql

  SELECT 'CFS to Zero' Location, [0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],              
[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]  FROM                 
(SELECT  Trailer_RegNo,Time FROM #tt )as Tab1                
PIVOT                
(                
max(Trailer_RegNo) FOR Time IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],              
[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) AS Tab2 

For above query i’m getting data which is returning only 1 row. The image is attached below enter image description here

But i need multiple rows as a result from the below table enter image description here

I need a output as

Location    0        1       2       3       4       5      6       7       8        9        10         11       12        13          14          15              16     17       18        19         20      21       22       23
CFS to Zero NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL        NULL    KA108112    TN52C4788   TN04AH3243  TN04AB6915  TN03H9079   NULL    NULL    NULL    NULL    NULL    NULL    NULL
 CFS to Zero    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL        NULL    KA108112    TN28C3709   TN04AK3631  TN04J6646   TN04AE7461  NULL    NULL    NULL    NULL    NULL    NULL    NULL
etc

Advertisement

Answer

No it is not possible to do PIVOT without an aggregate function, but you can make the aggregate function arbitrary by ensuring there is only one source row per group. It is not immediately clear from your question what your logic is though:

For example, you have two values for a time of 12 (KA108112 and KA106360), and 3 rows for a time of 13 (TN04F6726, TN28C3709, TN52C4788), so there are a few different ways you could combine these two.

You could return the cartesian product:

12          13
----------------------
KA108112    TN04F6726
KA106360    TN04F6726
KA108112    TN28C3709
KA106360    TN28C3709
KA108112    TN52C4788
KA106360    TN52C4788

Or you could return each result once, e.g.

12          13
----------------------
KA106360    TN04F6726
KA108112    TN28C3709
NULL        TN52C4788

But you also need to provide additional logic, i.e. why would KA106360 match with TN04F6726, rather than any of the other two values (in the above example I have ordererd Trailer_RegNo alphabetically within each time, but there could be any number of ways of doing this.

Based on your comment, it seems like you want to arbitralily pair values, so in order to make sure that every value is represented you need to make each row unique, which you can do using ROW_NUMBER(), e.g.

DECLARE @T TABLE (Time INT, Trailer_RegNo VARCHAR(20));
INSERT @T (Time, Trailer_RegNo)
VALUES (12, 'KA108112'), (12, 'KA106360'), (13, 'TN04F6726'), (13, 'TN28C3709'), (13, 'TN52C4788');

SELECT  Trailer_RegNo, 
        Time,
        RowNumber = ROW_NUMBER() OVER(PARTITION BY [Time] ORDER BY Trailer_RegNo) 
FROM    @T;

Which gives:

Trailer_RegNo   Time    RowNumber
----------------------------------
KA106360        12      1
KA108112        12      2
TN04F6726       13      1
TN28C3709       13      2
TN52C4788       13      3

Now you have a way of pairing your items (i.e. KA106360 & TN04F6726, KA108112 & TN28C3709 etc). To change how items are grouped, you would just need to change the ORDER BY clause in the ROW_NUMBER() funciton.

So your final query might be something like:

DECLARE @T TABLE (Time INT, Trailer_RegNo VARCHAR(20));
INSERT @T (Time, Trailer_RegNo)
VALUES (12, 'KA108112'), (12, 'KA106360'), (13, 'TN04F6726'), (13, 'TN28C3709'), (13, 'TN52C4788'),
        (14, 'KA108112'), (15, 'KA106360'), (15, 'TN04F6726'), (1, 'TN28C3709'), (0, 'TN52C4788');

SELECT  'CFS to Zero' Location, 
        [0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],              
        [13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]
FROM    (   SELECT Trailer_RegNo, 
                    Time,
                    RowNumber = ROW_NUMBER() OVER(PARTITION BY [Time] ORDER BY Trailer_RegNo) 
            FROM    @T 
        ) AS Tab1                
        PIVOT                
        (   MAX(Trailer_RegNo) 
            FOR [Time] IN 
            (   [0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],              
                [13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]
            )
        ) AS Tab2;
6 People found this is helpful
Advertisement