Crosstab query(intersection table)?

I can use crosstab query(intersection table) in sql server.

for example :

CREATE TABLE [Test] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[Source] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

INSERT INTO [test] ([name],[subject],[Source]) values (N'mike,N'math,60)

INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'English',70)

INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'france',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'jane',N'English',75)

INSERT INTO [test] ([name],[subject],[Source]) values (N'mary',N'france',57)

INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'math',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'Englist',100)

Go

--

declare @.sql varchar(8000)

set @.sql = 'select name,'

select @.sql = @.sql + 'sum(case subject when ''+subject+''

then source else 0 end) as ''+subject+'','

from (select distinct subject from test) as a

select @.sql = left(@.sql,len(@.sql)-1) + ' from test group by name'

exec(@.sql)

But I cannot use it in sql server mobile editon.

HOw do I? thank you very much!!!

Suggest you review the SQL Mobile Books OnLine - there is a complete "SQL Reference for SQL Mobile" reference chapter there that will outline your options. The support for the CASE function in SQL Mobile is more basic than what you are trying to do here.

Darren

|||

Thank you, Darren Shaffer.

I do review SQL Reference for SQL Mobile. But I cannot find the method in sql mobile.

Yes , It can work well use case function . But the colums is not determinate. so I have to use :

declare @.sql varchar(8000)

set @.sql = 'select name,'

select @.sql = @.sql + 'sum(case subject when ''+subject+''

......////

The query can work well in sql server , but it cannot work in sql server mobile.

How do I ?

我非常困惑，请帮助解决好吗？谢谢了。

|||

I try to use crosstab query(intersection table) by sql server mobile in .netCF in my application. But write crosstab query is very hard for me in sql mobile editon.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=277223&SiteID=1&mode=1

Must it be not carried out throuth sql query.May be it can do throught programming ？If that , How can I do it ? By the way ,I can only use C#.

|||

My advice would be to make the determination of the appropriate query to run in your application code (not in SQL) and then submit the appropriate query string to SQL Mobile.

Darren

|||

Moving this thread to the SQL Mobile forum.

I am also going to merge it with the other thread.