Confused reading / attempting VERY simple SQL Pivot...aggregation required?

Question

I have a table where multiple rows are needing to be used as columns. Google tells me PIVOT is for just this sort of thing. I start by reading MSDN / Technet. This seems straight forward and mentions...

and performs aggregations where they are required

So I start to write my pivot only their examples all use aggregation functions. So I go back to Google for some other examples that might not use aggregation and every example is using aggregation...so I begin to wonder did I read the definition correctly? I thought aggregation was not REQUIRED just available.

and performs aggregations where they are required....but I don't require any so....?

My table looks like this:

SELECT * FROM [ADHOC_FIELD] where TEMPLATE_ID = 356

CONTACT_ID  |  FIELD_NAME  |  TEMPLATE_ID  |  VALUE  |  ROW_ID
1017674        Orig_Name          356          John        1
1017674        Orig_EMail         356          @.com       1
1017674        Orig_Trans         356          ABC         1
1017674        Orig_WFID          356          1001        1

I would like the data to present like this:

CONTACT_ID | Orig_Name | Orig_Email | Orig_Trans | Orig_WFID
  1017674     John        @.com         ABC         1001

As I wrote this SO prompted me with several post about pivot without aggregation but they were all more complex than my simple little requirement.

This one seemed like a winner but even then they used row_number and then the aggregate function MAX?

So I'm not asking for anyone to do my work just trying to understand....does PIVOT require aggregation or not?

EDIT

So based upon everyone's feedback I wrote this PRIOR to Zoff Dino answer.

SELECT
   [1] AS 'recv_date',
   [2] AS 'send_date',
   [3] AS 'proc_name',
       ....etc
FROM
(
   SELECT VALUE, ROW_NUMBER() OVER (ORDER BY CONTACT_ID) rn
   FROM [ADHOC_FIELD] WHERE TEMPLATE_ID = 356
) src
PIVOT
(
   MAX(VALUE)
   FOR rn IN ([1],[2],[3],...)
) piv

And it was working but ONLY returning 1 record. However as Zoff Dino pointed out I wasn't quite straight in my thinking and his answer is perfect.

Yes, an aggregate function is required in the `PIVOT` statement, even if it is not required. I typically use MAX() on string values, but I usually sit that on a CTE which eliminates duplicate values as required -- making the MAX() useless. — Dave C, Oct 08 '14 at 17:50
If you wrote a query that didn't use pivot but did group on some fields, then any field that was not part of your 'group by' would have to be contained in an aggregate function -- even if that row was unique. Pivot behaves this same way. — Ryan B., Oct 08 '14 at 17:54

score 2 · Accepted Answer · answered Oct 08 '14 at 18:30

PIVOT requires an aggregate function. Your case is simply a layout change. In these cases, MAX is often used. You can also use MIN for the same effect.

SELECT  CONTACT_ID, Orig_Name, Orig_Email, Orig_Trans, Orig_WFID
FROM    ADHOC_FIELD
PIVOT   (
            MAX(Value) FOR FILED_NAME IN (Orig_Name,Orig_Email,Orig_Trans,Orig_WFID)
        ) pvt
WHERE   pvt.TEMPLATE_ID = 356

score 0 · Answer 2 · answered Oct 08 '14 at 18:00

If you are using SQL 2005 or newer version

With Tmp AS
(
SELECT * FROM [ADHOC_FIELD] where TEMPLATE_ID = 356
)
Select
 (Select top 1 CONTACT_ID From Tmp) CONTACT_ID
 ,(Select VALUE   From Tmp Where FIELD_NAME = 'Orig_Name' ) [Orig_Name]
 ,(Select VALUE   From Tmp Where FIELD_NAME = 'Orig_Email' ) [Orig_Email]
 ,(Select VALUE   From Tmp Where FIELD_NAME = 'Orig_Trans' ) [Orig_Trans]
 ,(Select VALUE   From Tmp Where FIELD_NAME = 'Orig_Trans' ) [Orig_Trans]

Otherwise change the first line of code into variable or temporary table

Confused reading / attempting VERY simple SQL Pivot...aggregation required?

2 Answers2