After getting already really good answer to this topic in the past (here and here). It seems that I'm still not able to get me head around this pivot stuff (I also didn't used it for a long time).
So hopefully someone is (again) able to tell me how to get the data into the desired format:
Given data:
ID | Label | Occurences | RangeBegin | RangeEnd | Unit
---+-------+------------+------------+----------+--------
1 | One | 0 | -1000 | 0 | m
1 | One | 5 | 0 | 10 | m
1 | One | 8 | 10 | 20 | m
1 | One | 6 | 20 | 30 | m
1 | One | 15 | 30 | 40 | m
1 | One | 0 | 40 | 1000 | m
2 | One | 0 | -1000 | 0 | m
2 | One | 2 | 0 | 10 | m
2 | One | 13 | 10 | 20 | m
2 | One | 27 | 20 | 30 | m
2 | One | 5 | 30 | 40 | m
2 | One | 0 | 40 | 1000 | m
1 | Two | 0 | -1000 | 0 | kg
1 | Two | 4 | 0 | 2 | kg
1 | Two | 6 | 2 | 4 | kg
1 | Two | 1 | 4 | 6 | kg
1 | Two | 0 | 6 | 1000 | kg
2 | Two | 0 | -1000 | 0 | kg
2 | Two | 8 | 0 | 2 | kg
2 | Two | 1 | 2 | 4 | kg
2 | Two | 3 | 4 | 6 | kg
2 | Two | 0 | 6 | 1000 | kg
Desired result:
ID | One | OneRangeBegin | OneRangeEnd | OneUnit | Two | TwoRangeBegin | TwoRangeEnd | TwoUnit
---+-----+---------------+-------------+---------+------+---------------+-------------+----------
1 | 0 | -1000 | 0 | m | 0 | -1000 | 0 | kg
1 | 5 | 0 | 10 | m | 4 | 0 | 2 | kg
1 | 8 | 10 | 20 | m | 6 | 2 | 4 | kg
1 | 6 | 20 | 30 | m | 1 | 4 | 6 | kg
1 | 15 | 30 | 40 | m | 0 | 6 | 1000 | kg
1 | 0 | 40 | 1000 | m | null | null | null | null
2 | 0 | -1000 | 0 | m | 0 | -1000 | 0 | kg
2 | 2 | 0 | 10 | m | 8 | 0 | 2 | kg
2 | 13 | 10 | 20 | m | 1 | 2 | 4 | kg
2 | 27 | 20 | 30 | m | 3 | 4 | 6 | kg
2 | 5 | 30 | 40 | m | 0 | 6 | 1000 | kg
2 | 0 | 40 | 1000 | m | null | null | null | null
Also to make the work on it a little bit easier, I put the data above at SqlFiddle.
I think it can be achieved by doing something like this:
- Split the Table based on Labels
- Rank the each table by partitioning on ID and arranging it based on RangeBegin.
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY RangeBegin) - Full join the Two Tables on ID and Rank
SELECT
A.ID AS ID
,A.Occurences AS One
,A.RangeBegin AS OneRangeBegin
,A.RangeEnd AS OneRangeEnd
,A.Unit AS OneUnit
,B.Occurences AS Two
,B.RangeBegin AS TwoRangeBegin
,B.RangeEnd AS TwoRangeEnd
,B.Unit AS TwoUnit
FROM
(select
*, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY RangeBegin) Rank
from
AvailableData
wHere Label = 'One') A
FULL JOIN
(select
*, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY RangeBegin) Rank
from
AvailableData
Where Label = 'Two' ) B
ON A.ID = B.ID
AND A.Rank = B.Rank
ORDER BY ISNULL(A.ID, B.ID), ISNULL(A.Rank, B.Rank)
