Tablas Pivoteadas con Multiples valores.

[Libras]

aqui esta la solucion con un query dinamico, aclaro el query dinamico nada mas es para obtener los valores de las columnas a pivotear, no ejecutaras el query 1 vez cada dia....y como es cadena se obtiene rapido y no consume mucho el servidor, lo probe en mi server local y para obtener las fechas hasta el 30 de abril del 2014 tardo 2 segundos :P

Código SQL:

CREATE TABLE #temp
(
consecutivo VARCHAR(20),
trabajador VARCHAR(20),
labor1 INT,
labor2 INT,
labor3 INT
)
 
INSERT INTO #temp VALUES ('01','001' ,1,0,0)
INSERT INTO #temp VALUES ('01','002' ,0,1,0)
INSERT INTO #temp VALUES ('01','003' ,0,0,1)
INSERT INTO #temp VALUES ('01','004' ,0,1,0)
 
 
INSERT INTO #temp VALUES ('02','001',0,0,1)
INSERT INTO #temp VALUES ('02','002',0,0,1)
INSERT INTO #temp VALUES ('02','003',0,1,0)
INSERT INTO #temp VALUES ('02','004',1,0,0)
 
 
INSERT INTO #temp VALUES ('03','001',1,0,0)
INSERT INTO #temp VALUES ('03','002',1,0,0)
INSERT INTO #temp VALUES ('03','003',1,0,0)
INSERT INTO #temp VALUES ('03','004',1,0,0)
 
 
INSERT INTO #temp VALUES ('04','001',0,1,0)
INSERT INTO #temp VALUES ('04','002',0,1,0)
INSERT INTO #temp VALUES ('04','003',1,0,0)
INSERT INTO #temp VALUES ('04','004',0,0,1)
 
INSERT INTO #temp VALUES ('05','001',1,0,1)
INSERT INTO #temp VALUES ('05','002',1,1,1)
INSERT INTO #temp VALUES ('05','003',1,1,0)
INSERT INTO #temp VALUES ('05','004',1,0,1)
 
 
INSERT INTO #temp VALUES ('06','001',0,0,0)
INSERT INTO #temp VALUES ('06','002',0,1,0)
INSERT INTO #temp VALUES ('06','003',0,1,0)
INSERT INTO #temp VALUES ('06','004',0,0,0)
 
 
INSERT INTO #temp VALUES ('07','001',1,1,0)
INSERT INTO #temp VALUES ('07','002',0,1,0)
INSERT INTO #temp VALUES ('07','003',1,0,0)
INSERT INTO #temp VALUES ('07','004',0,1,1)
 
INSERT INTO #temp VALUES ('08','001',0,0,1)
INSERT INTO #temp VALUES ('08','002',0,0,1)
INSERT INTO #temp VALUES ('08','003',1,0,0)
INSERT INTO #temp VALUES ('08','004',1,0,0)
INSERT INTO #temp VALUES ('08','005',0,0,1)
 
 
 
CREATE TABLE #temp2
(
consecutivo VARCHAR(20),
fecha datetime
)
 
INSERT INTO #temp2 VALUES ('01','2013-11-01')
INSERT INTO #temp2 VALUES ('02','2013-11-02')
INSERT INTO #temp2 VALUES ('03','2013-11-03')
INSERT INTO #temp2 VALUES ('04','2013-11-04')
INSERT INTO #temp2 VALUES ('05','2013-11-05')
INSERT INTO #temp2 VALUES ('06','2013-11-06')
INSERT INTO #temp2 VALUES ('07','2013-11-07')
INSERT INTO #temp2 VALUES ('08','2013-11-08')
 
 
DECLARE @inicial datetime
DECLARE @inicial2 datetime
DECLARE @final datetime
DECLARE @x INT
DECLARE @dias INT
DECLARE @pivot VARCHAR(MAX)
DECLARE @header VARCHAR(MAX)
 
 
SET @inicial='2013-11-01'
SET @inicial2=@inicial
SET @final='2013-11-08'
SET @dias=datediff(dd,@inicial,@final)
 
 
SET @pivot=''
SET @header=''
SET @x=1
while @x<=@dias
BEGIN
SET @pivot=@pivot + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + '],'
SET @header=@header + 'sum(' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']) as ' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + '],'
SET @inicial=dateadd(dd,1,@inicial)
    IF @dias=@x
        BEGIN
            SET @pivot=@pivot + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']'
            SET @header=@header + 'sum(' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']) as ' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']'
        END
SET @x=@x+1
END
 
 
DECLARE @query Nvarchar(MAX)
SET @query='select trabajador,' + @header + '
from
(
    SELECT trabajador,' + @pivot +' FROM
    (
    SELECT t1.consecutivo,t1.trabajador,
    case when labor1=1 then 1 
    when labor2=1 then 2 
    when labor3=1 then 3 end as labor,
    fecha FROM #temp as t1
    LEFT JOIN #temp2 as t2 on (t1.consecutivo=t2.consecutivo)
    WHERE fecha BETWEEN ' + CHAR(39) + REPLACE(CONVERT(VARCHAR(20),@inicial2,111),'/','-') + CHAR(39) + ' and ' + CHAR(39) +  REPLACE(CONVERT(VARCHAR(20),@final,111),'/','-') + CHAR(39) + '
    ) AS source
    pivot
    (
    max(labor) FOR fecha IN (' + @pivot +')
    ) AS pvt 
) as t1 group by trabajador'
 
 
EXEC sp_executesql @query
 

saludos!

P.S: Lo del paypal no es broma......