Pivot using single column and no aggregate function

Question

I have a doubt. I have a column called "ID". In that column I have values like "FieldName" followed by "FromDate", "Value" followed by "2012/12/01" , "FieldName" followed by "ToDate" Value "2013/12/01" etc.,

**ID column**    
        FieldName
        FromDt
        Value
        2010/12/01
        FieldName
        ToDt
        Value
        2013/12/21
        FieldName
        CreatedDt
        Value
        2012/10/01
        FieldName
        ModifyDt
        Value
        2013/01/02

Now I want a table like

**FieldName  Value**
FromDt      2010/12/01
ToDt        2013/12/21
CreatedDt   2012/10/01
ModifyDt    2013/01/02

Is it possible to use pivot with a single column and without aggregate function? Kindly suggest me how can I do this? (either using Pivot or some other methods)

Regards, T.N.Nagasundar

@Magnus for each 4 rows, 2nd is field name and 4th is field value — Aprillion, Apr 11 '13 at 16:32
A query does not have an order unless `OrderBy` is specified. — Magnus, Apr 11 '13 at 16:35
To expand on what @Magnus is saying, you can't guarantee an order unless there's another column to reference; even if you always physically insert the rows in the correct order and usually get the correct order when you select out of the table, you're eventually going to get burned by a query returning slightly (or vastly) out of order). — Jason Whitish, Apr 11 '13 at 17:05

score 1 · Answer 1 · answered Apr 11 '13 at 16:52

You should have another column do Order By as @Magnus suggested.

Otherwise, try this. SQLFiddle

WITH cte AS
(
  SELECT ID,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num
  FROM tbl
 )
SELECT c1.ID AS [field_name], c2.ID AS [value]
FROM cte c1
INNER JOIN cte c2
  ON c2.row_num = c1.row_num + 2
WHERE (c1.row_num % 4) = 2

score 0 · Answer 2 · edited May 23 '17 at 12:04

I have no idea about using pivot in this situation. It is possible to split original-order rows into groups of 4 and select every 2nd and 4th row from each group like this:

http://sqlfiddle.com/#!3/b2af8/13/0

with t2 as (
  select
    id,
    (row_number() over(order by (select 0)) - 1) / 4 grp,
    (row_number() over(order by (select 0)) - 1) % 4 row
  from t
)
select a.id FieldName,
  b.id Value
from t2 a
join t2 b
  on a.grp = b.grp
  and a.row = 1 -- 2nd row numbering from 0
  and b.row = 3

(inspired by https://stackoverflow.com/a/6390282/1176601)

Pivot using single column and no aggregate function

2 Answers2