by Stephen Horsfield
11. March 2008 10:54
Overview
I've logged a suggestion with Microsoft for support for the inclusion of NULLs when you use the UNPIVOT operator. Add your support if you think this would be useful:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332325
Example
Source data
| ID |
Col1 |
Col2 |
Col3 |
Col4 |
| 1 |
Value |
NULL |
Value |
NULL |
Current behaviour
UNPIVOT (value FOR column_name IN (Col1, Col2, Col3, Col4))
| ID |
column_name |
value |
| 1 |
Col1 |
Value |
| 1 |
Col3 |
Value |
Suggested behaviour with new INCLUDE NULL optional clause
UNPIVOT (value FOR column_name IN (Col1, Col2, Col3, Col4) INCLUDE NULL)
| ID |
column_name |
value |
| 1 |
Col1 |
Value |
| 1 |
Col2 |
NULL |
| 1 |
Col3 |
Value |
| 1 |
Col4 |
NULL |
This feature is available in Oracle 11g, for example.
Versions
- SQL Server 2008
- SQL Server 2005
Metadata
- Categories: IT Management, Software Development, SQL Server
- Additional keywords: UNPIVOT, NULL
- Technorati Tags: SQL, SQL Server, UNPIVOT, NULL
Subscribe