Re: Access Query Transform.. Pivot to SQL Server

T-SQL 2000 doesn't offer support for the PIVOT and TRANSFORM statements; you
will have to write your own stuff for doing this. As a start, you may want
to take a look at the following references:

http://www.ftponline.com/archives/premier/mgznarch/vbpj/2001/10oct01/sqlpro0110/rj0110/rj0110-1.asp

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q175574

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_04j7.asp

http://www.sqlteam.com/item.asp?ItemID=2955


--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Mastercafe - Juan" <vbnet@xxxxxxxxxxxxxx> wrote in message
news:OpgRbLyiGHA.4884@xxxxxxxxxxxxxxxxxxxxxxx
Hi group i have this sql in a MDB that run perfect.
TRANSFORM Count([dbo_Incidencias tecnicas].Clave) AS CountOfClave
SELECT [dbo_Clientes y referencias].IdCliente, [dbo_Clientes y
referencias].Empresa, [dbo_Clientes y
referencias].FechaFinContratoMantenimientoHardware, dbo_Cuentas.Nombre
FROM ([dbo_Clientes y referencias] LEFT JOIN [dbo_Incidencias tecnicas] ON
[dbo_Clientes y referencias].IdCliente=[dbo_Incidencias
tecnicas].IdCliente) LEFT JOIN dbo_Cuentas ON [dbo_Incidencias
tecnicas].Tecnico=dbo_Cuentas.IdCuenta
GROUP BY [dbo_Clientes y referencias].IdCliente, [dbo_Clientes y
referencias].Empresa, [dbo_Clientes y
referencias].FechaFinContratoMantenimientoHardware, dbo_Cuentas.Nombre
PIVOT Format([Fecha],"mm");

I make part of translation to sql, so i don't know how to put Transform
and Pivot

SELECT TOP 100 PERCENT dbo.[Clientes y referencias].IdCliente,
dbo.[Clientes y referencias].Empresa,
dbo.[Clientes y
referencias].FechaFinContratoMantenimientoHardware, DATEPART(mm,
dbo.[Incidencias tecnicas].Fecha) AS Meses,
COUNT(dbo.[Incidencias tecnicas].Clave) AS
Asistencias
FROM dbo.[Clientes y referencias] LEFT OUTER JOIN
dbo.[Incidencias tecnicas] ON dbo.[Clientes y
referencias].IdCliente = dbo.[Incidencias tecnicas].IdCliente
GROUP BY dbo.[Clientes y referencias].IdCliente, dbo.[Clientes y
referencias].Empresa, dbo.[Clientes y
referencias].FechaFinContratoMantenimientoHardware,
DATEPART(mm, dbo.[Incidencias tecnicas].Fecha)

--
Saludos desde Oviedo (Asturias)

Juan Menéndez
Mastercafe S.L.
www.mastercafe.com
juan@xxxxxxxxxxxxxx

Si la información recibido te ha servido indicalo con otro post.
En caso de resolverlo por otros medios, indica la solución usada
ayudaras a otros y aprenderemos todos.





.

Relevant Pages

  • Re: A good crosstab query reference
    ... PIVOT expression IN ... TRANSFORM SUMAs SumByAmount ... a NULL is supplied in the CELL. ... >reference for crosstab queries? ...
    (microsoft.public.access.queries)
  • Re: Finding Overlapping Data from a Table
    ... PIVOT c.GroupNum; ... the "field expressions" in the join ... TRANSFORM CountAS CountOfISBN ... in the same order as the row terms, ...
    (microsoft.public.access.queries)
  • Access Query Transform.. Pivot to SQL Server
    ... Hi group i have this sql in a MDB that run perfect. ... TRANSFORM CountAS CountOfClave ... Si la información recibido te ha servido indicalo con otro post. ... ayudaras a otros y aprenderemos todos. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Expressions in Crosstab Queries
    ... TRANSFORM COUNTAs theValue ... number of PIVOT expressions that supply a not-null value, for that group, ... If you aggregate it, it would supply just ... > wizard and learned crosstab queries. ...
    (microsoft.public.access.queries)
  • Re: A good crosstab query reference
    ... I appreciate the in-depth information about TRANSFORM and PIVOT. ... > You need the MAX to satisfy the TOTAL query requirement: ... > group, the Pivot value does not exist, a NULL is supplied in the CELL. ...
    (microsoft.public.access.queries)