Write Cross Tab Query in SQL view without using Transform.

[ssingh75]

Hi All,

I am using below query in Access database to create cross tab query.

TRANSFORM Sum(sn.NAV) AS SumOfNAV
SELECT sn.NavDate
FROM dbo_Scheme AS s LEFT JOIN dbo_Scheme_NAV AS sn ON s.SchemeCode = sn.schemecode
WHERE (((sn.NavDate)>=#1/1/2011#) AND ((s.Active)='Y'))
GROUP BY sn.NavDate, s.Active
ORDER BY s.schemecode
PIVOT s.schemecode;

I want to use the same string in SQl but Transform is not in SQL so how can i use the above string in SQL without transform.

Pls suggest.

 

[xenou]

Possibly search for google hits on TSQL PIVOT (if you are using sql server here). Such as this one:
http://www.tsqltutorials.com/pivot.php

another more complex approach if PIVOT doesn't work:
http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

 

[ssingh75]

Thanks for the same.

But when i am declaring the variable for more than 1l character its getting an erro.

I want to declare variable for more than 1 lakh characters.


Please suggest.

 

[Kreszch68]

By SQL, do you mean SQL Server? In that case have a look at the RAC utility.