Crystal syntax date query

Does anyone know what the formula is to search for records between 01/04/04 and the end of the last full month?
Thanks.Does anyone know what the formula is to search for records between 01/04/04 and the end of the last full month?

i assume that date1 is ur field at the table1 that need to be search for the formula..at ur report->edit selection formula->record..at this

{table1.date} in date(year,month,day) to date(year,month,day)

regards

Crystal Show SQL Query Question

I had a database named X. I renamed it to Y.

My report is mapped to a view named viewX.

After I changed database names, and I choose Show SQL Query in crystal reports, my query from clause says

FROM X.DBO.VIEWX

I want it to say Y.DBO.VIEWX since i changed database names. Is there any easy way to do this. Or is it possible to just eliminate the DB name from the SQL from clause.

thanks,

deep.Open the Report and Do verify databasesql

Crystal Reports Group By and MySQL mediumtext field

Hello Friends,

I have connected Crystal Reports to MySQL database 4.x.
Now I want to run a query using the group by option on a mediumtext data type field. But as Crystal Reports does not allow to do a Group by on a mediumtext field is there any way i can convert the field to String and then do a group by ?

Thanks for your help in advance.
Shanker.Do you have unique id value for that column?
If so group by itsql

crystal reports 8.5

Hi all,

This is my query in SQL Server

select customerid from v_verifiedtrips where startdatetime>'7/1/2005' and
enddatetime<'7/31/2005' and customerid not in (
select customerid from v_verifiedtrips where startdatetime>'6/1/2005' and enddatetime<'6/31/2005')

Could any one help me out in writing formula in Crystal reports 8.5 using above queryDid you mean selection formula?

CR.selectionFormula="startdatetime>'6/1/2005' and enddatetime<'6/31/2005'"

Crystal Reports 10 Viewers

I have a report (Crystal Reports 10) that connects to an Access database. The Access database uses a pass-through query to retrieve data. The .rpt file and the Access database will both be stored in the same directory.

What kind of viewer will my end-users need? Where can I get it?

ThanksCan anyone give some feedback on this? I still haven't found a solution...

Crystal Reports

HI, i am looking for some kind of a gateway where i can query the db2 on os390(mainframe) and then with the resultset i should be able to do reporting through crystal reports,can any one help me acheiving this?are you just wanting to run the report from the Crystal environment, or are you using an application written with VB or .NET??
Also, version of Crystal would be good to know, and the OS.

I can help with ODBC connection in Win OS, if you run into problems, but ideally you should first get your SysOp or DataBase admin to set up your ODBC driver and point it to the correct library files within db2.

I have just done battle with appalling and inaccurate Crystal documentation relating to connecting to AS400 running db2, via a small VB6 application that triggers automatic generation of reports in PDF format to users share on our company site. So I have some immediate experience with this minefield :-)))

Crystal documentation was misleading at best and bloody useless for the rest of the time.

Dave|||Hi,Thanks for your response.I want to use Crystal Reports itself to read the data from the DB2/os390 mainframe system.I am using crystal reports from a unix box.
Thanks
Ravi

Crystal Report Query Engine error

Hi,
My project is being done in Vb.Net 2003 and SQL Server2000. When I try to access the Crystal Report thru code only I get the query engine error. the Sql Query however works perfectly in Query Analyser.
The Query uses left outer join operation from 4 tables. Is there any way I can overcome my error?

Thanks in advance
JohnsyIt could be useful to know exactly what error message says, to figure out if we're dealing with connection problem, or there is an error in query.
Also, you could try running Profiler to see what happens on SQL Server when you run your report.|||hi johnsy,

i too got the same problem . please tell me the solution if had solved tat problem.
how to create dataset.xsd for 2 tables

Crystal Report based on SQL Query!

Hi all,

I'm working on Crystal Reports in C#. (Ofcourse ... this section of Forums is on VB... But still... i may geta solution for my prob!!)

I need to generate a report based on data retrieved from an SQL Query. Every time I need to pass this Query dynamically to the report, I mean, my SQL Query is not constant but changes depending on certain criteria.

How can I let the Report know this changing Query??
I could generate a report based on a fixed SQL Query. But How to send this dynamically changing Query to the report??

Pls help me out.

Thank you all.

Bye.i dunno about C# but, u can do it in VB.Net by setting the dataset as report source.

may be it can be done in C# too.

Already answered how to do it in VB.net, in this forum.|||Mr. Addidas

Hope the field names and number of fields are same eventhough query is changing.

Why don't you go for procedure with parameters. You can pass parameter dynamically to procedure. In VB the crystal report control provide this facility by ".StoredProcParam(0)" property. obviously you can give any number of parameter.

Second method is you can pass total query as string parameter to procedure.

Crystal report 8.5 help

i m using access 2000 from there i m calling crystal report 8.5 , every thing working fine except one query havib=ng having Criteria with parameters , how to pass this parameter to the Crystal report.

would some body help me

SELECT Assignment.BNo, Trade.Trade, Assignment.Source, Assignment.MobDate, Project.ProjectID
FROM Trade INNER JOIN (Project INNER JOIN (Orders INNER JOIN (Employees INNER JOIN Assignment ON Employees.[Badge Number] = Assignment.BNo) ON Orders.OrderID = Assignment.OrderNo) ON Project.ProjectID = Orders.ProjectID) ON Trade.[Serial No] = Assignment.Trade
GROUP BY Assignment.BNo, Trade.Trade, Assignment.Source, Assignment.MobDate, Project.ProjectID, Month([Assignment].[mobdate]), Year([Assignment].[mobdate])
HAVING (((Assignment.MobDate)=[enter date]) AND ((Month([Assignment].[mobdate]))=Month(Date())) AND ((Year([Assignment].[mobdate]))=Year(Date())))
ORDER BY Assignment.BNo;

this is the queryWhile writing the query, u may have some option to create parameter.

CR 9 has that option. No idea about CR 8.5

CrossTable - SQL Server 2000 Help me

Hello,

sorry for my English.. i have this problem

two tables

TABLE_A

Import

65

132

244

165

TABLE_B

FromValue ToValue

0 100

101 200

201 1000

i'd like a query that have to result this

Field1 Field2 Field3 (Count of table A)

0 100 1

101 200 2

201 1000 1

and if i change the table B this query automatically change..

thank's very much

Try

SELECT FromValue ToValue,
(SELECT COUNT(*) FROM TableA A WHERE A.ImportBETWEEN B.FromValue AND B.ToValue) AS Cnt
FROM TableB

|||Thank's very much

Crosstab?

Hi,
I run the following query "SELECT * FROM tbl_A", and get the following
result set:
Surname Firstname
Smith John
Smith Richard
Smith Simon
What I want to see in my result set is:
Surname Firstname
Smith John, Richard, Simon
How do I achieve this?
Thanks
Jon Derbyshire
Can you do it in the client? This kind of thing is extremely inefficient in
SQL Server.
If you must do it in SQL Server, refer to the following:
http://www.aspfaq.com/show.asp?id=2529
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
"Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
> Hi,
> I run the following query "SELECT * FROM tbl_A", and get the following
> result set:
> Surname Firstname
> Smith John
> Smith Richard
> Smith Simon
> What I want to see in my result set is:
> Surname Firstname
> Smith John, Richard, Simon
> How do I achieve this?
> Thanks
> Jon Derbyshire
|||Thats brilliant - Thanks
"Adam Machanic" wrote:

> Can you do it in the client? This kind of thing is extremely inefficient in
> SQL Server.
> If you must do it in SQL Server, refer to the following:
> http://www.aspfaq.com/show.asp?id=2529
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
> news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
>
>

Crosstab?

Hi,
I run the following query "SELECT * FROM tbl_A", and get the following
result set:
Surname Firstname
Smith John
Smith Richard
Smith Simon
What I want to see in my result set is:
Surname Firstname
Smith John, Richard, Simon
How do I achieve this?
Thanks
Jon DerbyshireCan you do it in the client? This kind of thing is extremely inefficient in
SQL Server.
If you must do it in SQL Server, refer to the following:
http://www.aspfaq.com/show.asp?id=2529
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
--
"Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
> Hi,
> I run the following query "SELECT * FROM tbl_A", and get the following
> result set:
> Surname Firstname
> Smith John
> Smith Richard
> Smith Simon
> What I want to see in my result set is:
> Surname Firstname
> Smith John, Richard, Simon
> How do I achieve this?
> Thanks
> Jon Derbyshire|||Thats brilliant - Thanks
"Adam Machanic" wrote:

> Can you do it in the client? This kind of thing is extremely inefficient
in
> SQL Server.
> If you must do it in SQL Server, refer to the following:
> http://www.aspfaq.com/show.asp?id=2529
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in messag
e
> news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
>
>

Crosstab. 2 totals ?

Hi,

Am using robvolks crosstab-procedure to generate a crosstab query.

I get this result:
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342

So I get a total for each month. But I would also like a total of each
letter
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
Total 1601 4 337 1260

Is that possible?

/jim
--call to procedure
execute crosstab 'select DATENAME(month,(theDate)) as '' '', count(*) as
'MonthsTotal'' from tblData group by
DATENAME(month,(theDate))','count(letter)','letter ','tblData'

----Robvolks procedure--
CREATE PROCEDURE crosstab
@.select varchar(8000),
@.sumfunc varchar(100),
@.pivot varchar(100),
@.table varchar(100),
@.where varchar(1000)='1=1'

AS

DECLARE @.sql varchar(8000), @.delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET LANGUAGE Danish

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE
1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table +
' WHERE '
+ @.where + ' AND ' + @.pivot + ' Is Not Null')

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date',
data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '
+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM
##pivot

DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)
SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql
+ ' ')

EXEC (@.select)
SET ANSI_WARNINGS ON
GOJim Andersen (jba020@.politiSLET.dk.invalid) writes:

Quote:

Originally Posted by

Am using robvolks crosstab-procedure to generate a crosstab query.
>
I get this result:
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
>
So I get a total for each month. But I would also like a total of each
letter
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
Total 1601 4 337 1260
>
Is that possible?

You would use INSERT EXEC to capture the result from the crosstab
procedure into a temp table, and then compute a total row from the
data in it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;

But why not use a report writer in the front end, like you are supposed
to?|||--CELKO-- wrote:

Quote:

Originally Posted by

SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;
>
But why not use a report writer in the front end, like you are
supposed to?

So I don't have to hardcode my A, B and C's ?
Because I am using Visual Studio .NET and that leaves me with Crystal
Reports (yuckk, hark, spit) as a reporting tool.

I think I will try Erlands suggestion.

/jim

Crosstab, Pivot Query representation

Hello,

I need help with data representation.

I have a query :

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

The above query returns the following results:
USERLOGIN SOURCE DBUSERNAME

Don Crilly FC8 Don Crilly
Don Crilly ACT Donald Crilly
Don Crilly SFS Don Crilly

I need the output in following format:

USERLOGIN ACT FC8 SFS
-
Don Crilly Donald Crilly Don Crilly Don Crilly

Can you please guide me as to what I should do to achive the required results.

Thanks.

If you use SQL Server 2005:
SELECT * FROM (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) p
PIVOT (MIN(DBUSERNAME) FOR SOURCE IN ([FC8], [ACT], [SFS])) pvt
--Or You can use the following for earlier versions
select a.USERLOGIN
, min(case a.SOURCE when 'FC8' then DBUSERNAME end) as [FC8]
, min(case a.SOURCE when 'ACT' then DBUSERNAME end) as [ACT]
, min(case a.SOURCE when 'SFS' then DBUSERNAME end) as [SFS]
from (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) as a
group by a.USERLOGIN|||

If you're using SQL Server 2005, then you can use the PIVOT operator to do this.

For example, this will work for your query:

SELECT *

FROM (SELECT USERLOGIN, SOURCE, DBUSERNAME

FROM TBL_USER

WHERE USERLOGIN LIKE 'Don Crilly'

) SOURCEQUERY

PIVOT (

MIN(DBUSERNAME)

FOR SOURCE IN ([ACT], [FC8], [SFS])

) AS PIVOTTABLE

The restrictions for using the PIVOT operator are that the PIVOT clause must include an aggregate operator (the MIN in this case, which will work unless a single USERLOGIN and SOURCE combination can have multiple DBUSERNAMEs associated with it) and that the resulting column names (ACT, FC8 and SFS) must be listed explicitly (unlike MS Excel, for example, which just creates columns for every value in the pivotting column).

Hope that gives you a start at least.

Iain

|||

This query will do..

Select * from dbo.TBL_USER
PIVOT
(
Min(DBUserName) For Source in ([FC8],[ACT],[SFS])
) as T
Where UserLogin = 'Don Crilly'

|||Thanks a lot :)

CrossTab(?) Query Problem

I've got a fairly tricky report to produce, so I'm trying to build it up bit
by bit, but I've fallen at the first hurdle!
I need to produce a view that lists all PartNo's and for each, what quantity
is in what location, e.g.
PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
WE12024, 5, null, 3, 10, 18
WE12067, 1, 1, null, 5, 7
etc...
I have the SQL to extract each bit of the data, but the problem is
presenting it in this way.
SQL:
Select PartNo, Count(*) as InGoodsIn
from Filters
Where LocationID = 15 /* for GoodsIn */
The PostInspection, Quarrantine & Stock locations have the following
LocationIDs - 16, 14, 17
How do I combine these result into one structure as indicated? Can somebody
point me in the right direction,please?
[In case it isn't clear from the above, the Filters table has this
structure: SerialNo (primary key), PartNo, LocationID]
Thanks in advance
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%237q365qlFHA.360@.TK2MSFTNGP09.phx.gbl...
> I've got a fairly tricky report to produce, so I'm trying to build it up
> bit by bit, but I've fallen at the first hurdle!
> I need to produce a view that lists all PartNo's and for each, what
> quantity is in what location, e.g.
> PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
> WE12024, 5, null, 3, 10, 18
> WE12067, 1, 1, null, 5, 7
> etc...
> I have the SQL to extract each bit of the data, but the problem is
> presenting it in this way.
> SQL:
> Select PartNo, Count(*) as InGoodsIn
> from Filters
> Where LocationID = 15 /* for GoodsIn */
> The PostInspection, Quarrantine & Stock locations have the following
> LocationIDs - 16, 14, 17
> How do I combine these result into one structure as indicated? Can
> somebody point me in the right direction,please?
> [In case it isn't clear from the above, the Filters table has this
> structure: SerialNo (primary key), PartNo, LocationID]
> Thanks in advance
>
Apologies - problem solved.
I was on the right lines but had made a couple of errors. The solution I
have is:
SELECT Partno,
[GoodsIn] = SUM(CASE LocationID WHEN 15 THEN 1 ELSE 0 END),
[PostInspection] = SUM(CASE LocationID WHEN 16 THEN 1 ELSE 0 END),
[Quarantine] = SUM(CASE LocationID WHEN 14 THEN 1 ELSE 0 END),
[Stock] = SUM(CASE LocationID WHEN 17 THEN 1 ELSE 0 END)
FROM Filters
GROUP BY PartNo|||Try,
select
PartNo,
sum(case when location = 15 then 1 else 0 end) as GoodsIn,
sum(case when location = 16 then 1 else 0 end) as PostInspection,
sum(case when location = 14 then 1 else 0 end) as Quarrantine ,
sum(case when location = 17 then 1 else 0 end) as Stock
from
Filters
order by
PartNo
go
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"CJM" wrote:

> I've got a fairly tricky report to produce, so I'm trying to build it up b
it
> by bit, but I've fallen at the first hurdle!
> I need to produce a view that lists all PartNo's and for each, what quanti
ty
> is in what location, e.g.
> PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
> WE12024, 5, null, 3, 10, 18
> WE12067, 1, 1, null, 5, 7
> etc...
> I have the SQL to extract each bit of the data, but the problem is
> presenting it in this way.
> SQL:
> Select PartNo, Count(*) as InGoodsIn
> from Filters
> Where LocationID = 15 /* for GoodsIn */
> The PostInspection, Quarrantine & Stock locations have the following
> LocationIDs - 16, 14, 17
> How do I combine these result into one structure as indicated? Can somebod
y
> point me in the right direction,please?
> [In case it isn't clear from the above, the Filters table has this
> structure: SerialNo (primary key), PartNo, LocationID]
> Thanks in advance
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:191A665E-2D8E-43F0-845D-EDAAAD54C844@.microsoft.com...
> Try,
> select
> PartNo,
> sum(case when location = 15 then 1 else 0 end) as GoodsIn,
> sum(case when location = 16 then 1 else 0 end) as PostInspection,
> sum(case when location = 14 then 1 else 0 end) as Quarrantine ,
> sum(case when location = 17 then 1 else 0 end) as Stock
> from
> Filters
> order by
> PartNo
> go
>
Curious...
Your syntax is different to mine but still works - eg. "Case when LocationID
= 15" rather than "Case LocationID when 15"
Is this difference significant?
Chris|||The result of both expressions are the same. See CASE in BOL to read more
about "simple case" and "search case" formats.
AMB
"CJM" wrote:

> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:191A665E-2D8E-43F0-845D-EDAAAD54C844@.microsoft.com...
> Curious...
> Your syntax is different to mine but still works - eg. "Case when Location
ID
> = 15" rather than "Case LocationID when 15"
> Is this difference significant?
> Chris
>
>

crosstab(?) query

I am trying to do a query on the following data:
Acct Units Person
10 5 Bob
10 6 Ted
10 3 Sam
11 4 Jill
12 4 Mary
I want my recordset to look like:
Acct 1 2 3 . . . .
10 Ted Bob Sam
11 Jill
12 Mary
In other words, my columns would be 1st, 2nd, 3rd, etc... for each
person/account, based on units.
I currently do this by dumping my data in Excel, doing some manipulations,
exporting up into Access and doing a crosstab query. I am hoping I can do
this in one process in SQL. Can someone point me in the right direction?
thanks
Dion
Have a look here:
http://www.aspfaq.com/show.asp?id=2462
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Dion" <Dion@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F2BE3DD9-41BC-4001-8E81-677731C4569A@.microsoft.com...
>I am trying to do a query on the following data:
> Acct Units Person
> 10 5 Bob
> 10 6 Ted
> 10 3 Sam
> 11 4 Jill
> 12 4 Mary
> I want my recordset to look like:
> Acct 1 2 3 . . . .
> 10 Ted Bob Sam
> 11 Jill
> 12 Mary
> In other words, my columns would be 1st, 2nd, 3rd, etc... for each
> person/account, based on units.
> I currently do this by dumping my data in Excel, doing some manipulations,
> exporting up into Access and doing a crosstab query. I am hoping I can do
> this in one process in SQL. Can someone point me in the right direction?
> thanks
> Dion

crosstab(?) query

I am trying to do a query on the following data:
Acct Units Person
10 5 Bob
10 6 Ted
10 3 Sam
11 4 Jill
12 4 Mary
I want my recordset to look like:
Acct 1 2 3 . . . .
10 Ted Bob Sam
11 Jill
12 Mary
In other words, my columns would be 1st, 2nd, 3rd, etc... for each
person/account, based on units.
I currently do this by dumping my data in Excel, doing some manipulations,
exporting up into Access and doing a crosstab query. I am hoping I can do
this in one process in SQL. Can someone point me in the right direction?
thanks
DionHave a look here:
http://www.aspfaq.com/show.asp?id=2462
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Dion" <Dion@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F2BE3DD9-41BC-4001-8E81-677731C4569A@.microsoft.com...
>I am trying to do a query on the following data:
> Acct Units Person
> 10 5 Bob
> 10 6 Ted
> 10 3 Sam
> 11 4 Jill
> 12 4 Mary
> I want my recordset to look like:
> Acct 1 2 3 . . . .
> 10 Ted Bob Sam
> 11 Jill
> 12 Mary
> In other words, my columns would be 1st, 2nd, 3rd, etc... for each
> person/account, based on units.
> I currently do this by dumping my data in Excel, doing some manipulations,
> exporting up into Access and doing a crosstab query. I am hoping I can do
> this in one process in SQL. Can someone point me in the right direction?
> thanks
> Dion

crosstab(?) query

I am trying to do a query on the following data:
Acct Units Person
10 5 Bob
10 6 Ted
10 3 Sam
11 4 Jill
12 4 Mary
I want my recordset to look like:
Acct 1 2 3 . . . .
10 Ted Bob Sam
11 Jill
12 Mary
In other words, my columns would be 1st, 2nd, 3rd, etc... for each
person/account, based on units.
I currently do this by dumping my data in Excel, doing some manipulations,
exporting up into Access and doing a crosstab query. I am hoping I can do
this in one process in SQL. Can someone point me in the right direction?
thanks
DionHave a look here:
http://www.aspfaq.com/show.asp?id=2462
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Dion" <Dion@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F2BE3DD9-41BC-4001-8E81-677731C4569A@.microsoft.com...
>I am trying to do a query on the following data:
> Acct Units Person
> 10 5 Bob
> 10 6 Ted
> 10 3 Sam
> 11 4 Jill
> 12 4 Mary
> I want my recordset to look like:
> Acct 1 2 3 . . . .
> 10 Ted Bob Sam
> 11 Jill
> 12 Mary
> In other words, my columns would be 1st, 2nd, 3rd, etc... for each
> person/account, based on units.
> I currently do this by dumping my data in Excel, doing some manipulations,
> exporting up into Access and doing a crosstab query. I am hoping I can do
> this in one process in SQL. Can someone point me in the right direction?
> thanks
> Dion

Cross-Tab vs SQL query

Hi,
I have a report like this...

Date Sam Tom Harry 2006 2005
02/01/2006 4 1 1 6 2
02/02/2006 3 1 1 4 5
02/03/2006 2 2 0 4 4
---
---

02/12/2006 1 1 1 3 1
-----------
Total -- -- -- -- --

Now I have all the data required for that in one table.

CREATE TABLE t1(
indate DATETIME
,agname VARCHAR(60)
,Polnumber VARCHAR(20)
)


So I have 2 options :
I have to do in in Cross tab or I have to create a query for this.
Is it possible to built a query like this,I mean assigning data as column?

Any help will be greatly appreciated.
Thanks!!Is it possible to built a query like this,I mean assigning data as column?Yep. If there are a fixed number of possible column values you can use CASE. If not then you need to use dynamic SQL (e.g. http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21820764.html#16500817,
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608,
http://www.sqlteam.com/item.asp?ItemID=2955). I've not used any of the above - I just have them bookmarked in case I need them.

BTW - your better option might be the front end doing this if at all possible

HTH|||Thanks a lot pottie for those links.I will give it a try tonight...Thanks once again..:cool:

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.