0

I have a table like this:

ID | Date 1     | Date 2     | Date 3
1  | 2014-08-01 | 2014-08-02 | 2014-08-03

And I need to output it like this:

ID | Date Field Name | Date Value
1  | Date 1          | 2014-08-01
1  | Date 2          | 2014-08-02
1  | Date 3          | 2014-08-03

Have tried dynamic unpivoting with unions but seems messy. Is there a best practice way of doing this?

sqlstudent
  • 101
  • 4

1 Answers1

1

I think UNPIVOT is the best practice here. I don't find it messy so much as confusing, maybe because I don't reach for it that often. This will give the results you're looking for:

SELECT ID, [Date Field Name], [Date Value]
FROM myTable
UNPIVOT ([Date Value] FOR [Date Field Name] IN ([date 1], [date 2], [date 3])) AS x
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • Thanks, I had some problems with this query I think because one of the fields was a computed column but I managed to get round it was using my original dynamic sql query. – sqlstudent Oct 27 '14 at 12:39