TSQL Query Pivoting

Question

J'ai une structure de tableau (carte de score de golf) comme suit :

HoleID int
CourseID int
Par INT
Distance INT
LowIndex INT
HighIndex INT
Sequence INT (representing the sequence of the hole: 1, 2, 3, ... 18)

Ce parcours comporte 18 trous.

Je veux pivoter vers :

Hole,      1, 2, 3, ... 18
Par,       X, X, X, ... X
Distance,  Y, Y, Y, ... Y
LowIndex,  Z, Z, Z, ... Z
HighIndex, A, A, A, ... A

Je peux y parvenir en utilisant le curseur, mais existe-t-il un meilleur moyen ?

Answer 1

TSQL prend directement en charge le pivotement. Voir à ce sujet aquí y aquí par exemple.

Answer 2

Vous pouvez faire

SELECT HoleID FROM Card WHERE  HoleId =1 ,(SELECT  HoleID   FROM Card WHERE  HoleId =2) , -- etc  

UNION
SELECT Par FROM Card WHERE HoleId = 1, (SELECT Par FROM Card where HoleID=2). --etc
UNION
SELECT Distance FROM Card WHERE HoleId = 1, (SELECT Distance FROM Card where HoleID=2). --etc
UNION
SELECT LowIndex FROM Card WHERE HoleId = 1, (SELECT LowIndex FROM Card where HoleID=2). --etc
UNION
SELECT HIghIndex FROM Card WHERE HoleId = 1, (SELECT HighIndex FROM Card where HoleID=2). --etc

si vous vouliez absolument vous débarrasser du curseur.

Veillez à ce que tous les types de données soient identiques par le biais d'un cast ou autre.

Answer 3

Je n'ai obtenu que la première partie ... cela suffira.

select [1],[2],[3],[4],[5],[6],[7],[8],[9], [10], [11], [12], [13], [14], [15], [16], [17], [18]
from (Select Sequence, Par FROM holes WHERE CourseID=1) a

PIVOT 
(
    Max(Par)
    FOR [Sequence] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9], [10], [11], [12], [13], [14], [15], [16], [17], [18])

) AS p

Answer 4

Liste complète :

select 'Par', [1],[2],[3],[4],[5],[6],[7],[8],[9], [10], [11], [12], [13], [14], [15], [16], [17], [18]
from (Select Sequence, Par FROM holes WHERE CourseID=1) a

PIVOT 
(
    Max(Par)
    FOR [Sequence] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9], [10], [11], [12], [13], [14], [15], [16], [17], [18])

) AS p

UNION

select 'Distance', [1],[2],[3],[4],[5],[6],[7],[8],[9], [10], [11], [12], [13], [14], [15], [16], [17], [18]
from (Select Sequence, Distance FROM holes WHERE CourseID=1) a

PIVOT 
(
    Max(Distance)
    FOR [Sequence] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9], [10], [11], [12], [13], [14], [15], [16], [17], [18])

) AS p

UNION

select 'LowIndex', [1],[2],[3],[4],[5],[6],[7],[8],[9], [10], [11], [12], [13], [14], [15], [16], [17], [18]
from (Select Sequence, LowIndex FROM holes WHERE CourseID=1) a

PIVOT 
(
    Max(LowIndex)
    FOR [Sequence] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9], [10], [11], [12], [13], [14], [15], [16], [17], [18])

) AS p

UNION

select 'HighIndex', [1],[2],[3],[4],[5],[6],[7],[8],[9], [10], [11], [12], [13], [14], [15], [16], [17], [18]
from (Select Sequence, HighIndex FROM holes WHERE CourseID=1) a

PIVOT 
(
    Max(HighIndex)
    FOR [Sequence] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9], [10], [11], [12], [13], [14], [15], [16], [17], [18])

) AS p