Access to sql crosstab query to sql

Question

I am trying to convert this access query to sql 2008

Can anyone convert this access query to sql?

TRANSFORM Sum(q_INTERMONTH_Union_ILIs.Total) AS SumOfTotal
SELECT q_INTERMONTH_Union_ILIs.[Billable Identifier], 
   q_INTERMONTH_Union_ILIs.[Entity Identifier]
FROM q_INTERMONTH_Union_ILIs
GROUP BY q_INTERMONTH_Union_ILIs.[Billable Identifier], 
   q_INTERMONTH_Union_ILIs.[Entity Identifier]
PIVOT q_INTERMONTH_Union_ILIs.Period;

but getting this error... anyone help

See http://stackoverflow.com/questions/11617713/how-to-create-a-pivottable-in-transact-sql — Fionnuala, Jan 30 '13 at 00:13

Taryn · Answer 1 · 2013-01-30T10:26:27.840

0

You current query in SQL Server will be similar to this:

select *
from
(
  SELECT q_INTERMONTH_Union_ILIs.[Billable Identifier], 
    q_INTERMONTH_Union_ILIs.[Entity Identifier],
    q_INTERMONTH_Union_ILIs.Period
  FROM q_INTERMONTH_Union_ILIs
) src
pivot
(
  Sum(Total)
  for Period in (yourPeriodValues go here)
) piv

In this line:

for Period in (yourPeriodValues go here)

You will place the values that you want as the columns inside of the parentheses (yourPeriodValues go here).

edited Jan 30 '13 at 10:26

answered Jan 30 '13 at 00:27

Taryn

242,637
56
362
405

What ever I put in (yourPeriodValues go here) I get this error: "The column prefix 'q_INTERMONTH_Union_ILIs' does not match with a table name or alias named used in the query" and it repeats 2 time – user2023568 Jan 30 '13 at 03:21
@user2023568 see my edit, I the table name needs to be removed from the `pivot` part. – Taryn Jan 30 '13 at 10:27

Access to sql crosstab query to sql

1 Answers1