# 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

But i need multiple rows as a result from the below table

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
```

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