با استفاده از دستور Pivot می توان از داده های سطر ها به عنوان ستون استفاده کرد:
جدول زیر را در نظر بگیرید:
SELECT ID, LoadStateID, HourID, Value
FROM HourlyLoadState
| ID | LoadStateID | HourID | Value |
| 1 | 1 | 1 | L |
| 2 | 1 | 2 | L |
| 3 | 1 | 3 | L |
| 4 | 1 | 4 | L |
| 5 | 1 | 5 | L |
| 6 | 1 | 6 | M |
| 7 | 1 | 7 | M |
حالا ساعت ها را بجای ردیف در ستون نمایش می دهیم:
select LoadStateID
, [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4
, [5] as Hour5, [6] as Hour6, [7] as Hour7, [8] as Hour8
, [9] as Hour9, [10] as Hour10, [11] as Hour11, [12] as Hour12
, [13] as Hour13, [14] as Hour14, [15] as Hour15, [16] as Hour16
, [17] as Hour17, [18] as Hour18, [19] as Hour19, [20] as Hour20
, [21] as Hour21, [22] as Hour22, [23] as Hour23, [24] as Hour24
from
(
select LoadStateID,HourID,Value
from HourlyLoadState
) x
pivot
(
max(Value)
for HourID in([1],[2],[3],[4], [5], [6],[7],[8],[9],
[10],[11],[12],[13],[14],[15], [16],
[17],[18],[19],[20],[21],[22],[23],[24])
)p
و خروجی به صورت زیر می شود:
| LoadStateID | Hour1 | Hour2 | Hour3 | Hour4 | Hour5 | Hour6 | Hour7 |
| 1 | L | L | L | L | L | M | M |
| 3 | L | L | L | L | L | L | L |
| 4 | L | L | L | L | L | L | L |
همچنین با استفاده از UnPivot می توان از ستون ها به عنوان سطر استفاده کرد:
جدول زیر را در نظر بگیرید:
SELECT ID, Hour1, Hour2, Hour3, Hour4
FROM Test
| ID | Hour1 | Hour2 | Hour3 | Hour4 |
| 1 | 0 | 10 | 0 | 20 |
| 2 | 116.2 | 120.4 | 125.9 | 99.1 |
حالا ساعت ها را بجای ستون در ردیف نمایش می دهیم:
select ID,
IndicatorName,
IndicatorValue
from Test
unpivot
(
IndicatorValue
for IndicatorName in (Hour1, Hour2, Hour3, Hour4)
) u;
| ID | IndicatorName | IndicatorValue |
| 1 | Hour1 | 0 |
| 1 | Hour2 | 10 |
| 1 | Hour3 | 0 |
| 1 | Hour4 | 20 |
| 2 | Hour1 | 116.2 |
| 2 | Hour2 | 120.4 |
| 2 | Hour3 | 125.9 |
| 2 | Hour4 | 99.1 |
برچسب ها