Transposing Table

问题:

I've looking everywhere and have not finding anything useful.

I have a table the captures assistance for employees.

The table that looks like this:

ID   | DATE     | ATTENDANCE
________________
2524 | 20121001 | ASISTANCE
2525 | 20121001 | ABSCENCE
2526 | 20121001 | ASISTANCE
2527 | 20121001 | ASISTANCE
2524 | 20121002 | ASISTANCE
2525 | 20121002 | ABSCENCE
2526 | 20121002 | ASISTANCE
2527 | 20121002 | ASISTANCE
2524 | 20121003 | ASISTANCE
2525 | 20121003 | DAY OFF
2526 | 20121003 | DAY OFF
2527 | 20121003 | ASISTANCE

And I want a query that returns a table that will look like this:

ID   | 20121001  | 20121002  | 20121003
________________
2524 | ASISTANCE | ASISTANCE | ASISTANCE
2525 | ABSCENCE  | ABSCENCE  | DAY OFF
2526 | ASISTANCE | ASISTANCE | ASISTANCE
2527 | ASISTANCE | ASISTANCE | DAY OFF

I tried individual querys and joining them, but since they are to many dates it takes too much to do so.

How can I do it that is efficient and can be stored into a view or function??

回答1:

It would be easier to get the data and process it in a server-side language like PHP. It would then be a trivial matter to build the array:

$entry[$id][$date] = $status;

Then:

echo "ID";
foreach(array_keys(array_values($entry)[0]) as $date) {
    // requires some temporary variables in PHP before 5.4
    echo "\t".$date;
}
foreach($entry as $id=>$days) {
    echo "\n".$id;
    foreach($days as $day) echo "\t".$day;
}

You now have a tab-separated table.

回答2:

Actually, this can be done with the PIVOT function. There are two ways to use PIVOT, either static or dynamic.

Static Pivot, you will hard-code the values to turn into columns:

create table tablea
(
    id int,
    dt datetime,
    attendance varchar(20)
);

insert into tablea values
(2524 , '20121001' , 'ASISTANCE'),
(2525 , '20121001' , 'ABSCENCE'),
(2526 , '20121001' , 'ASISTANCE'),
(2527 , '20121001' , 'ASISTANCE'),
(2524 , '20121002' , 'ASISTANCE'),
(2525 , '20121002' , 'ABSCENCE'),
(2526 , '20121002' , 'ASISTANCE'),
(2527 , '20121002' , 'ASISTANCE'),
(2524 , '20121003' , 'ASISTANCE'),
(2525 , '20121003' , 'DAY OFF'),
(2526 , '20121003' , 'DAY OFF'),
(2527 , '20121003' , 'ASISTANCE');

select *
from
(
    select id, dt, attendance
    from tablea
) x
pivot
(
    max(attendance)
    for dt in ([2012-10-01], [2012-10-02], [2012-10-03])
) p;

A dynamic PIVOT will get the list of values at run-time and will be more flexible if the list is changing:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(char(10), dt, 120)) 
                    from tablea
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, ' + @cols + ' from 
             (
                select id, dt, attendance
                from tablea
            ) x
            pivot 
            (
                max(attendance)
                for dt in (' + @cols + ')
            ) p '

execute(@query)

Both will give the same result:

id   | 2012-10-01 | 2012-10-02 | 2012-10-03
-------------------------------------------
2524 | ASISTANCE  | ASISTANCE  | ASISTANCE
2525 | ABSCENCE   | ABSCENCE   | DAY OFF
2526 | ASISTANCE  | ASISTANCE  | DAY OFF
2527 | ASISTANCE  | ASISTANCE  | ASISTANCE