PIVOT i CTE – zestawienie dysków z komputera (macierz informacyjna)

27-Maj-2015

Problem: Zapytanie zwraca informacje o komputerach i dyskach w kolejnych wierszach. Chcemy, aby nazwy dysków powędrowały do kolumn.

Rozwiązanie:

Załóżmy, że struktura tabeli wygląda następująco:

CREATE TABLE #disks
(machineid int,
 letter CHAR(2),
 size int,
 label char(16),
 comment char(20))

CREATE TABLE #disks

(machineid int,

letter CHAR(2),

size int,

label char(16),

comment char(20))

INSERT #disks VALUES(1,'c:',100,'system','backup'), (1,'d:',200,'data','backup')

1	INSERT #disks VALUES(1,'c:',100,'system','backup'), (1,'d:',200,'data','backup')

Żeby litery dysków pojawiły się w kolumnach użyj tabeli przestawnej:

SELECT machineid, [c:],[d:] FROM
(SELECT machineid, letter,label FROM #disks) x 
PIVOT
(MIN(label) FOR letter in ([c:],[d:])) p

SELECT machineid, [c:],[d:] FROM

(SELECT machineid, letter,label FROM #disks) x

PIVOT

(MIN(label) FOR letter in ([c:],[d:])) p

Aby zamiast label zobaczyć comment użyj:

SELECT machineid, [c:],[d:] FROM
(SELECT machineid, letter,comment FROM #disks) x 
PIVOT
(MIN(comment) FOR letter in ([c:],[d:])) p

SELECT machineid, [c:],[d:] FROM

(SELECT machineid, letter,comment FROM #disks) x

PIVOT

(MIN(comment) FOR letter in ([c:],[d:])) p

A jeśli chcesz zobaczyć i label i comment, to zastosuj jeszcze CTE:

with labels as
(
   SELECT machineid, [c:],[d:] FROM
   (SELECT machineid, letter,label FROM #disks) x 
   PIVOT
   (MIN(label) FOR letter in ([c:],[d:])) p
),
comments as
(
   SELECT machineid, [c:],[d:] FROM
   (SELECT machineid, letter,comment FROM #disks) x 
   PIVOT
   (MIN(comment) FOR letter in ([c:],[d:])) p
)
SELECT 
   l.machineid, l.[c:] as label_c, l.[d:] as label_d, 
   c.[c:] as comment_c, c.[d:] as comment_d 
FROM labels l
JOIN comments c on l.machineid = c.machineid