-2

I have a table that looks like this:

Table

+------+----------+
| CODE | DATE     |
+======+==========+
|  A   | 6/30/14  |
|  A   | 3/31/14  |
|  A   | 12/31/13 |
|  A   | 9/30/13  |
|  B   | 5/31/14  |
|  B   | 2/28/14  |
|  B   | 11/30/13 |
|  B   | 8/31/14  |
|  C   | 6/27/14  |
|  C   | 3/28/14  |
|  C   | 12/27/13 |
|  C   | 9/27/13  |
+------+----------+

I need the output to look like this:

Expected Output

+-------+-----------+----------+-----------+----------+
| CODE  |  DATE_1   | DATE_2   |  DATE_3   |  DATE_4  |
+=======+===========+==========+===========+==========+
|  A    |  6/30/14  | 3/31/14  | 12/31/13  | 9/30/13  |
|  B    |  5/31/14  | 2/28/14  | 11/30/13  | 8/31/14  |
|  C    |  6/27/14  | 3/28/14  | 12/27/13  | 9/27/13  |
+-------+-----------+----------+-----------+----------+

Each code value (A, B, C) is displayed 4 times, so the DATE_1, DATE_2, DATE_3, and DATE_4 fields do not change. Can someone help??

Ullas
  • 11,450
  • 4
  • 33
  • 50
pedro1181
  • 13
  • 3
  • What's the order of the four dates? Pivot them only by physical order? – Jaugar Chang Sep 21 '14 at 05:46
  • possible duplicate of [SQL Server Pivot with Dynamic Fields](http://stackoverflow.com/questions/14902626/sql-server-pivot-with-dynamic-fields) – Jaugar Chang Sep 21 '14 at 05:59
  • 1
    Almost any PIVOT/UNPIVOT question is a duplicate. OP, please look into some examples online (there are plenty incl on SO), and try it yourself at least. If you have problems with your query, post what you have. – TT. Sep 21 '14 at 06:23

1 Answers1

0

Using PIVOT can achieve this.

QUERY

SELECT code,
[1] AS DATE_1,
[2] AS DATE_2,
[3] AS DATE_3,
[4] AS DATE_4
FROM
(
  SELECT code, dt, 
  row_number() over 
  (
     partition by code 
     ORDER BY code
  ) rn
  FROM tbl
) t
PIVOT 
(
  MIN (dt) FOR rn IN 
  (
     [1], [2], [3], [4]
  )
) pvt

FIND FIDDLE HERE

Ullas
  • 11,450
  • 4
  • 33
  • 50
  • Thanks! This was very helpful and revealed a lot about how to manipulate the pivot and partition by code! – pedro1181 Sep 22 '14 at 03:06