asked
how to convert TRANSFORM query in SQLSERVER
The below query has been written in Access, i would like to convert that into Sqlserver2008. TRANSFORM Count(Pos) AS [The Value] SELECT ID, ClassCode, Count(Pos) AS TotalPo FROM rptEst GROUP BY ID, ClassCode PIVOT Citi In ("O","P","S","V","TOTAL");
Direct translation of the Access' transform / pivot to T-SQL is this query: select pt.* from (select ID, ClassCode, City, Pos from rptEst) src pivot (count(Pos) for src.City in ([O], [P], [S], [V], [TOTAL])) pt; This returns results with 7 columns named [ID], [ClassCode], [O], [P], [S], [V] and [TOTAL]. As you can see, there is no need to explicitly group by the source data. <\!-- **Begin Edit** I hope that by now I understand what needs to happen. The results are pivoted for each available value in the City column (of which there are 5 values, namely O, P, S, V and Total), wrapping the count(Pos) under respective column header. The results also need to include an extra column displaying the value equal to the sum of the values in other pivoted columns. For example, if current query returns something like this: ID ClassCode O P S V Total --- ---------- --- --- --- --- ----- 1 1 2 2 0 1 0 2 2 2 2 1 0 3 -- it should return one more column with a sum of other pivoted columns: -------------------------------------------- ID ClassCode O P S V Total TotalPo --- ---------- --- --- --- --- ----- ------- 1 1 2 2 0 1 0 5 2 2 2 2 1 0 3 8 If my guess is correct then there are a couple of ways to do it: Using PIVOT: select ID, ClassCode, isnull([O], 0) [O], isnull([P], 0) [P], isnull([S], 0) [S], isnull([V], 0) [V], isnull([Total], 0) [Total], [TotalPo] from ( select ID, ClassCode, City, 1 Pos from rptEst union all select ID, ClassCode, 'TotalPo', count(Pos) from rptEst group by ID, ClassCode ) src pivot (sum(Pos) for src.City in ([O], [P], [S], [V], [TOTAL], [TotalPo])) pt; Using the old school method with conditional case statements: select ID, ClassCode, count(case when City = 'O' then 1 else null end) [O], count(case when City = 'P' then 1 else null end) [P], count(case when City = 'S' then 1 else null end) [S], count(case when City = 'V' then 1 else null end) [V], count(case when City = 'Total' then 1 else null end) [Total], count(Pos) TotalPo from rptEst group by ID, ClassCode; Both should result in the very similar or trivially different execution plans. First query will work in SQL Server 2005 or better with compat level of 90 or better while the second query will work in any version of SQL Server starting from SQL Server 7. **End Edit** --> Oleg
