Crystal V9 Range selection

Hi all,

I have installed SP3. whilst in the development environment, I have created a simple report that just lists everything from a single table. This works fine.

I then introduced a range parameter on one of my fields. then go to preview and enter a valid range, but the results still show all records???

Cant believe this is a enuine bug, as it's pretty fundemental. What am I doing wrong?What datatype it is? I tried with date range. It works fine.|||the field type is a character string and the database is an Access 2000 one.

The values are 2 characters like:

LA LB LC LD LE ......and so on.

when I enter a start of LB and and end LD for instane, it returns All|||check ur selection formula.|||I have now removed all parameter fields and will walk you through what I am doing.

I have the field explorer open and have right clicked on the parameter fields and selected new.

this displays a create parameter field dialog box I then enter a value in the name and propting text values leaving value type as string. I change options to range values then press the button set default values. I ensure that the table is correct and then set the browse field with the column from the table that I want to filter on. Then in the left hand pane it displays all values relating to that column. I press the >> button to make all values the default I then set the order to alphabetical ascending i then press ok. it goes back to the previous dialog and I leave allow editing of default values checked and press ok.

My parameter field now exists in the field explorer window under parameters. I drag this to the report header, and press preview I'm them prompted for a start and end of range which I enter, I then press ok, and then I'm returned all values.

???:confused:|||Everythings looks correct.

The last step is, you have to write the record selection formula.

Goto Report menu, select "SelectFourmula" and then "Record..."

This will opens the edit formula window.

Here select your database field and then give in condition which will be suitable for range values and then select ur parameter field.
This looks like

{table.column} in {?parameter}

now save and close this.

Refrsh the report|||Ah....thx the manual doesn't mention the last bit. It works now.

Crystal Rpt 10 - currentdate + 6 months

Hey all I am trying to use Crystal Reports 10 and have a date that I need to use from my table. I want to take that date and if it is greater than the CURRENTDATE + 6 months I need to display a message. I have part of it but not sure how to do the "6 month" thing.

if ({mytable.mydate}) > CurrentDate ("+6 months") then
'Length-Promo-Rate = 0'

I have tried this:
CurrentDate + Month({mytable.mydate},6) but it doesn't like it. Suggestions? Thanks.Hi,
for that you have to insert a formula field like :

dim msg as string
if {Report Date/date from table} >= DateAdd ("m",6 , CurrentDate) then
msg ="6 month old"
else
msg = "new"

end if

formula = msg

-Jayesh Mendpara

Crystal Reports over network

Hi all,

I have received the following error message when tring to load a report which is accessing data over a network from sql server.

The table " could not be found.

Here is the code to try and access a number of reports;

Any help is much appreciated.
Thanks

Private Sub btnRunReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRunReport.Click

Try

MyBase.Cursor = Cursors.WaitCursor

If Me.cboReports.SelectedIndex = 0 Then
crvReports.ReportSource = DataAccess.getLevyDirectory & "\1.rpt"
ElseIf Me.cboReports.SelectedIndex = 1 Then
crvReports.ReportSource = DataAccess.getLevyDirectory & "\2.rpt"
ElseIf Me.cboReports.SelectedIndex = 2 Then
crvReports.ReportSource = DataAccess.getLevyDirectory & "\L3.rpt"
ElseIf Me.cboReports.SelectedIndex = 3 Then
crvReports.ReportSource = DataAccess.getLevyDirectory & "\4.rpt"
ElseIf Me.cboReports.SelectedIndex = 4 Then
crvReports.ReportSource = DataAccess.getLevyDirectory & "\5.rpt"
ElseIf Me.cboReports.SelectedIndex = 5 Then
crvReports.ReportSource = DataAccess.getLevyDirectory & "\6.rpt"
ElseIf Me.cboReports.SelectedIndex = 6 Then
crvReports.ReportSource = DataAccess.getLevyDirectory & "\7.rpt"

End If


'MsgBox("Please load pre-printed stationary into the printer.")

crvReports.PrintReport()
crvReports.RefreshReport()

MyBase.Cursor = Cursors.Arrow

Catch ex As Exception
MsgBox(ex.Message)
End Try


End Sub


End ClassOpen the report and do verify database and make sure it is pointing to the correct databasesql

Crystal Reports- last receipt date

Here is the situation: I have 3 tables.
Table 1: part number, unit of measure, standard cost
Table 2: last price paid, and Date of last price paid (same as last receipt date)
Table 3: PO# of last price paid (same as last receipt date)

When I insert these fields into the details section in Crystal Reports, I get duplicate part numbers listed on the report. Each part number may have several vendors attached to it, so this is expected.

This is what I am getting:

ItemNumber UM Std Cost LastCost LastReceiptDate PONumber
88888 Each 1.00 1.50 7/19/04 11111
88888 Each 1.00 1.80 7/22/04 22222
88888 Each 1.00 2.00 7/20/04 33333

What I want:

ItemNumber UM Std Cost LastCost LastReceiptDate PONumber
88888 Each 1.00 1.80 7/22/04 22222

I want the report to only show the most recent receipt date, price paid, and PO. Basically I need to do some sort of "comparison" between multiple vendors and its last receipt date within each part number.

Please help~~~~ Thank you!Hi,

I had faced same problem but I think if u used Command Object insted of tables
u can get solution.

write SQL query in Command Object and use MAX function on Receipt Date

-Yags

Crystal Reports in C# - doesnt show data

Hi @.ll,

I'm new to Crystal Reports... using VS 2005 Beta

I need a simple Report from my simple database - one table.
I created a Crystal Reports-Report and used a "Crystal Reports Viewer" in my Winform and everything seems to work.

When my program starts the Report is displayed, the header is displayed but no Detail-Data. That means: My Data isn't displayed.

You might now think: I haven't got any data in my table... But that isn't true, there are 2 datasets in my table...

What should I do?
Is there any Initializing that i haven't done? Why doesn't the Report showing my data automatically?

Thanks for help!

ITSEOpen the database Do verify Database
Uncheck the option "Save Data with Reports"|||Thanks for your answer !

Open the Database with which Program?
In VS 2005 there is the Tool "DataSources" for the Project-DataSources and at the tool for generating CrystalReports there is the "FieldExplorer".

But in none of them is a checkbox "Save Data ..."

Can you please explain where the checkbox is?

Thanks,

cya|||Open the database Do verify Database
Uncheck the option "Save Data with Reports"

I mean open that report and do that not the database|||ok ;-) then I should open the Report. I open it with VS and where is the checkbox? I looked in every dialog I can find, but I think you are using a other program?|||Well
Through coding give
CR.DiscardSavedData=True|||ok I wan't to try it.

In my Project I only got a WinForm, a CrystalReportViewer in it and a CrystalReport. (a "crystalReport1.rpt" File)

Where should I write the "DiscardSavedData"-Thing? I tried to find this Attribute in my classes but couldn't find it.

I know it looks like I'm stupid, but can you or someone show me in which File in which Class I should write your code?

Thanks... Please give me more input not only a simple "one-line" answer... it's really annoying that my Report doesn't work.

To overcome that problem I had to use an Access-Database, create a Access-Report and it worked... It could be sooooo simple...
But why o why... I think I begin to hate the programmers of crystalReports...!!!!

Hope somebody helps me out of this ****.

Thanks

Crystal Reports connection to a Bugzilla MySQL database - problem with links.

I'm trying to set up Crystal Reports with an ODBC connection to Bugzilla which I know works.

It is crashing if I add more than one table - either if I click on Links or OK.

Do I need to give the user any extra priveledges to see links between tables. Are there links between tables in the MySQL database in the same way you would see in a SQL Server database?

I've done searches such as:
bugzilla odbc links
and
mysql odbc links

but I didn't find anything...

Thanks for your help,

JamesNobody got any idea what I might be doing wrong? My MySQL server is on a Linux Fedora Core 5 machine if that helps...|||It could be smart linking.
Try 'File' menu, 'Options', 'Database' tab, uncheck 'Automatic Smart Linking' at the bottom of the 'Advanced Options' section.|||It could be smart linking.
Try 'File' menu, 'Options', 'Database' tab, uncheck 'Automatic Smart Linking' at the bottom of the 'Advanced Options' section.
I don't have an 'Automatic Smart Linking' option, I'm using Crystal Reports 9.2.0.448

crystal report.. on network application

i m using crystal report with vb...

i have designed a report with a table on it..
the table creates when user click on the preview button on the form..
named tmpAccountLedger..

but when 2 or more users click at the same time..with the different account specifie.d...
the report shows the same for all users..

what should i do..
plz suggest..
regards.What's the underlying database? Can you use a session table? Can you use some sort of id to uniquely name each table creation? I assume you drop it at the end somehow?|||For MSSQL, I use global temporary tables for such purposes. When developing a report, I create a normal "dummy" table with prefix "_tmp_" that can be used for building report. At runtime, I create global temporary table like "##SF93DXU2" with same structure, and replace the name of original dummy table with the name of actual temporary table. Works fine. You can even forget to drop the table afterwards and SQL will clean it up for you. In fact, there are situations where you can't drop the table yourself (for example, if temporary table is used in a subreport at the end of the report, then it is not queried immediately but only when the user reaches the page that contains subreport).

Crystal Report X & Access 2003 Db

I am running CR X and Access 2003 database.

I have a table defined as Employee and one of the fields is called RESDate and is defined as Date/Time.

The RESDate has a default value Date () - 1. Bascially, I am deriving yesterday's date. When I view the table in Access, I see yesterday's date in the field.

Now in CR X:
I made a new connection and brought in this Access database and this one table. I moved the RESDate to detail line.

I do not see yesterday's date in CR. I did browse of the field in CR X and it shows blanks also. Any ideas ?

Thanks for your help in advance.

Regards
LB1That sounds odd, in CR right click on the field name and select show type,
does the field show up as a string ? or as date / time or ? Also can you create a query, or a view and try reporting off the query / view ?

Crystal Report with Images

Hi,
I have the images in a folder and the path is stored in a table for each record. Each record in the table is associated with a different image. I'm able to load the images dynamically in the crystal report. But when i create a Package using the Package & Deployment wizard, the images are not being displayed. Instaed the name of the images is displayed in that area.
Actually I use the OLE object to dispaly the image. and below is the code I have written

I'm using VB 6 and Crystal Reports 8.5

Private Sub Section6_Format(ByVal pFormattingInfo As Object)
If Field5.Value <> "" Then Picture1.SetOleLocation Field5.Value
End Sub

where field5 is the Path field placed in the report.

Please help. Its very very very urgent.

Thanks & Regards,
Venkatesh.Rplz tell how did u manage to change the image dynamicaly with cr 8.5....its really very urgent|||Attached is a zip file containing a sample project. Hope this will help you.

Regards,
Venkatesh.R|||Attached is a Sample project as a zip file. Hope this will help you.|||thanx a lot for ur help....i'll give it a try...bye for now..

Crystal Report Indexing

My report is calculating the sum of spindles in spindle table for number of machines. Each machine contain 480 or above spindles.

When I calculate to total bags produced by number of machines during the month, it calculte wrong and shows very huge amount like 72480 instead of 142 or something else.

When I remove the field which calculate the total spindles then it shows correct sum of total bags.

I want to know what is the problem. Or what is the indexing scheme.

I have checked normalization and mapping of database tables both in crystal report and SQL Server 2000. These are correct.

ThankyouCan you post some sample data and the result you want

Crystal Report 10.0 help

I wanna be creat a table of contents for Reports. so plz give me a

suggestion how to create a table of contents in Crystal Report 10.0. We are using ASP.net as a front end and Sql Server 2005 as a backend. For generating report, I am using crystal Report 10.0.

I have require item # which if formatted like (1.2.1.1) where one is the category of the Question and 2 is the Question of that particular category and 1 is the subquestion of that particular Question and again 1 is the anonther SubQuestion of the particular subquestion ..

So I want to show the record like this ....



Item# Question Page No.

1.2 Does vendor require TCP connection ? 1

so plzz help me ...

Report has almost completed... All the question and subquestion are displayed successfully with responses and remediation. and has also generated a summary report of that question. Only TOC part has left.
I want to geneate a TOC that describe only that which question are appearring in which page no...

Thank you !What are your data types, ie your Table of contents is based on ??

Crystal 8.5 Summing question

In a one to many join How do you sum a field coming from the one table. example:
Table 1 has the following columns:
Employee id
check number
Gross pay
Table 2 has
Employee id
check number
ded code
ded amount
The data is as follows

employee id check number Gross Pay
1 1 100
1 2 100
1 3 100

employee id check number ded code ded amt
1 1 A 10
1 1 B 5
1 1 C 1

Each check would have the same 3 deductions.
When these tables are joined by employee id and check number you get a total of 9 records. If you wanted the total gross for the employee you can not just sum gross pay because you would get 900. The real gross for the employee should be 300.
I have been able to create a formula that gets the gross of 100 on one of the record and 0 on the other two records for each check, but Crystal will not let me sum that field.
Any help would be good.Try to create a Group on Employee ID so that it's not repeated. The records under an employee id will be displayed in Detail Section.

Create a summary field on gross pay and place it in Group Footer. this will returns the gross pay without duplication. To sum the check amout, create a separate sum field/running total field.

Hope this work.

crosstab?

I've made "crosstab" queries in Access - it this doable in sql'
My table has:
ID
TestNumber
Score
Each user can have 1-3 records, one for each of the 3 tests.
I need my output to look like this:
ID Test1 Test2 Test3
--
1 score1 score2 score3
2 score1
3 score1 score2
(not everyone will have taken the 2nd or 3rd test at the same time; score#
just indicates the testscore)
'
Thanks for any help or pointers.
-RYes, but it must be statically defined like so:
Select Id
, Min(Case When TestNumber = 1 Then Score End) As Test1
, Min(Case When TestNumber = 2 Then Score End) As Test2
, Min(Case When TestNumber = 3 Then Score End) As Test3
From TableName
Group By Id
Thomas
"r" <r@.r.com> wrote in message news:uB0xq6%23XFHA.2768@.tk2msftngp13.phx.gbl...d">
> I've made "crosstab" queries in Access - it this doable in sql'
> My table has:
> ID
> TestNumber
> Score
> Each user can have 1-3 records, one for each of the 3 tests.
> I need my output to look like this:
> ID Test1 Test2 Test3
> --
> 1 score1 score2 score3
> 2 score1
> 3 score1 score2
> (not everyone will have taken the 2nd or 3rd test at the same time; score#
> just indicates the testscore)
> '
> Thanks for any help or pointers.
> -R
>
>|||SELECT id,
SUM(CASE WHEN testnumber = 1 THEN score END) AS test1,
SUM(CASE WHEN testnumber = 2 THEN score END) AS test2,
SUM(CASE WHEN testnumber = 3 THEN score END) AS test3
FROM YourTable
GROUP BY id
David Portas
SQL Server MVP
--|||If you ever need to go beyond xtab 101 check out
the powerful RAC utility.Similar in concept to Access xtab
but goes way beyond with its options and features.Also
includes other functionality (ie. ranking options) made easy.
www.rac4sql.net

crosstab stored procedure

I have a table that has the following information like this:

Name Date 1st item 2nd item 3rd item 4th item 5th item
Defaul name 11/1/2005 100 0 0 0 0
Defaul name 11/2/2005 100 0 0 0 0
Defaul name 11/3/2005 99.69 0 0 0 0

I need to create a crosstab query (NOT using crystal report) that will display the information like:

11/1/2005 11/2/2005 11/3/2005
1st item 100.00 100.00 99.69
2nd item 0.00 0.00 0.00
3rd item 0.00 0.00 0.00
4th item 0.00 0.00 0.00
5th item 0.00 0.00 0.00

I have never used a crosstab query in sql server before. Please help!First, look up CROSSTAB in Books Online and you will see the general method for handling this using CASE statements.
Unfortunately you are going to have problems if your column headers are dynamic, which is often the case when you are grouping your columns by date values.
If, as your example implies, you are only dealing with one year's worth of data at a time, then you can group your columns by datepart(month...), which will give you 12 consistent column labels.
Avoid dynamic crosstab queries if at all possible (or until you upgrade to SQL Server 2005...). And though you apparently already know this, I have to say that dynamic crosstab functionality does not really belong in SQL server anyway, SQL Server 2005 not withstanding. It is a presentation issue.|||Thanks for your reply. Unfortunately, the column values have to be dynamic. There is an easy way to present this in crystal reports but unfortunately i have to write directly to an excel file from a stored procedure. I'm at a loss right now as to how to accomplish this presentation in a stored procedure with dynamic columns.|||Not to beat a dead horse, but look at ags crosstab and/or RAC (used to be replacement for access crosstab, but they changed their name).

Regards,

hmscott

Crosstab result

Hi there,

I'm using sql2005. I've got a table in tabular form and I need to convert it to crosstab format. I've tried to use pivot operator but i can't get the result i want. Because pivot operator needs to aggregate a field but what i need is Dim values as column names and DimCode as values within those columns as it is, no aggregations.

For example : This is tabular table.

Dim DimCode JrnlEntry GLDistId TrxDate GLPostDT TrxType AccountIndex DebitAmt CreditAmt Amount 4 2 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 4 11991 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 5 7 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 5 5 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 4 1 54225 1 31/05/2007 31/05/2007 1 44256 600 0 600 5 5 54225 1 31/05/2007 31/05/2007 1 44256 600 0 600 4 12821 54225 2 31/05/2007 31/05/2007 1 44256 400 0 400 5 4 54225 2 31/05/2007 31/05/2007 1 44256 400 0 400 4 3 54227 3 31/05/2007 31/05/2007 1 44256 0 500 -500 5 8 54227 3 31/05/2007 31/05/2007 1 44256 0 500 -500

And i want the end result as under..

jrnentry aaGLDistID TRXDATE GLPOSTDT aaTRXType Dim1 Dim2 Dim3 Dim4 Dim5 Dim6 Dim7 Dim8 Dim9 Dim10 ACTINDX DEBITAMT CRDTAMNT Amount 54222 1 25/05/2007 25/05/2007 1 0 0 0 2 7 0 0 0 0 0 44256 617 0 617 54222 1 25/05/2007 25/05/2007 1 0 0 0 11991 5 0 0 0 0 0 44256 617 0 617 54225 1 31/05/2007 31/05/2007 1 0 0 0 1 5 0 0 0 0 0 44256 600 0 600 54225 2 31/05/2007 31/05/2007 1 0 0 0 12821 4 0 0 0 0 0 44256 400 0 400 54227 3 31/05/2007 31/05/2007 1 0 0 0 3 8 0 0 0 0 0 44256 0 500 -500

Your help will much appreciated.

There is an excellent article about PIVOT by Peter Larsson.

Follow the link :

Pivot table for Microsoft SQL Server

Thanks

Naras.

|||

For quite a few examples of using PIVOT, do a search on this Forum for the keyword 'Pivot'. Amazing how often this question is asked, and how often folks don't seem to bother looking in the archives to find their answer.

|||

Thanks Naras,

I'm not looking for help in Pivot functionality of sql2005 as it won't be usefull for me. If you look at the first table, i want the first column Dim to be used for the new columns i.e. Dim1, Dim2, Dim3 will be columns in the crosstab result but then i want DimCode to appear under respective columns without any aggregations. In pivot you've to use aggregate function. Also in crosstab result if look at the record with jrnentry 54222, there are two lines with the same aaGLDistId i.e. i don't want to aggregate values within dim1, dim2 etc.

Thanks

Vivek

|||

Vivek,

You are wrong. You can achive it using the PIVOT Operator. See the below query..

Code Snippet

Create Table #data (

[Dim] int ,

[DimCode] int ,

[JrnlEntry] int ,

[GLDistId] int ,

[TrxDate] datetime ,

[GLPostDT] datetime ,

[TrxType] int ,

[AccountIndex] int ,

[DebitAmt] int ,

[CreditAmt] int ,

[Amount] int

);

Set Dateformat DMY

Insert Into #data Values('4','2','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('4','11991','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('5','7','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('5','5','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('4','1','54225','1','31/05/2007','31/05/2007','1','44256','600','0','600');

Insert Into #data Values('5','5','54225','1','31/05/2007','31/05/2007','1','44256','600','0','600');

Insert Into #data Values('4','12821','54225','2','31/05/2007','31/05/2007','1','44256','400','0','400');

Insert Into #data Values('5','4','54225','2','31/05/2007','31/05/2007','1','44256','400','0','400');

Insert Into #data Values('4','3','54227','3','31/05/2007','31/05/2007','1','44256','0','500','-500');

Insert Into #data Values('5','8','54227','3','31/05/2007','31/05/2007','1','44256','0','500','-500');

Select

[JrnlEntry]

,[GLDistId]

,[TrxDate]

,[GLPostDT]

,[TrxType]

,Isnull([1],0) Dim1

,Isnull([2],0) Dim2

,Isnull([3],0) Dim3

,Isnull([4],0) Dim4

,Isnull([5],0) Dim5

,Isnull([6],0) Dim6

,Isnull([7],0) Dim7

,Isnull([8],0) Dim8

,Isnull([9],0) Dim9

,Isnull([10],0) Dim10

,[AccountIndex]

,[DebitAmt]

,[CreditAmt]

,[Amount] From

(

Select

[Dim]

,[DimCode]

,[GLDistId]

,[JrnlEntry]

,[TrxDate]

,[GLPostDT]

,[TrxType]

,[AccountIndex]

,[DebitAmt]

,[CreditAmt]

,[Amount]

,Row_Number() Over (Partition By [Dim],[GLDistId],[JrnlEntry] Order By [Dim],[GLDistId],[JrnlEntry]) RowId

From

#data

) as Data

Pivot

(

Max([DimCode])

For [Dim] in

(

[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]

)

) As PVT

|||

Thanks Manivannan, much appreciated. Its returning correct result. I almost spent the whole day on this yesterday with frustration.

Regards,

Vivek

Crosstab question for a "properties" table

Most of the examples for crosstabs use pivot. It looks like aggregations are what they are used for. What about if I have a dynamic properties table. So there is a table Person and a table Property and a table PropertyPersonValue. Is there any way to create a crosstab table of Person with Properties listed in the column heading and the Values in the cells?

Sure, can you post some DDL and sample data?

AMB

|||

CREATE TABLE Person

(

PersonID INT IDENTITY(1,1) NOT NULL,

[Name] VARCHAR(200),

CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED (PersonID)

)

GO

CREATE TABLE PersonProperty

(

PersonPropertyID INT IDENTITY(1,1) NOT NULL,

[Name] VARCHAR(200),

CONSTRAINT [PK_PersonProperty] PRIMARY KEY CLUSTERED (PersonPropertyID)

)

GO

CREATE TABLE PersonPropertyValue

(

PersonID INT NOT NULL,

PersonPropertyID INT NOT NULL,

[Value] VARCHAR(1000),

CONSTRAINT [PK_PersonPropertyValue] PRIMARY KEY CLUSTERED (PersonID,PersonPropertyID),

CONSTRAINT [FK_PersonPropertyValue_PersonID] FOREIGN KEY (PersonID)

REFERENCES [Person],

CONSTRAINT [FK_PersonPropertyValue_PersonPropertyID] FOREIGN KEY (PersonPropertyID)

REFERENCES [PersonProperty],

)

GO

INSERT INTO PersonProperty ([Name]) VALUES ('Eye Color')

INSERT INTO PersonProperty ([Name]) VALUES ('Title')

INSERT INTO Person ([Name]) VALUES ('Joe Smith')

INSERT INTO Person ([Name]) VALUES ('Fred Thompson')

INSERT INTO Person ([Name]) VALUES ('Sally Hamilton')

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Brown'

FROM Person p,PersonProperty pp

WHERE p.Name='Joe Smith'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Blue'

FROM Person p,PersonProperty pp

WHERE p.Name='Fred Thompson'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Green'

FROM Person p,PersonProperty pp

WHERE p.Name='Sally Hamilton'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'CFO'

FROM Person p,PersonProperty pp

WHERE p.Name='Joe Smith'

AND pp.Name='Title'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Vice Presient'

FROM Person p,PersonProperty pp

WHERE p.Name='Fred Thompson'

AND pp.Name='Title'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Sr. Partner'

FROM Person p,PersonProperty pp

WHERE p.Name='Sally Hamilton'

AND pp.Name='Title'

SELECT p.Name,pp.Name AS PropertyName, ppv.Value

FROM Person p

LEFT JOIN PersonPropertyValue ppv

ON p.PersonID=ppv.PersonID

LEFT JOIN PersonProperty pp

ON pp.PersonPropertyID=ppv.PersonPropertyID

|||

Try:

declare @.columns nvarchar(max)

declare @.sql nvarchar(max)

set @.columns = stuff(

(

select ',' + cast(quotename([Name]) as nvarchar(max))

from dbo.PersonProperty

order by [Name]

for xml path('')

), 1, 1, '')

set @.sql = N'

select

*

from

(

SELECT

p.Name,pp.Name AS PropertyName, ppv.Value

FROM

dbo.Person p

LEFT JOIN

dbo.PersonPropertyValue ppv

ON p.PersonID=ppv.PersonID

LEFT JOIN

dbo.PersonProperty pp

ON pp.PersonPropertyID=ppv.PersonPropertyID

) as src

pivot

(

min(Value)

for PropertyName in (' + @.columns + N')

) as pvt

order by

[Name]

'

exec sp_executesql @.sql

go

Result:

Name Eye Color Title Fred Thompson Blue Vice Presient Joe Smith Brown CFO Sally Hamilton Green Sr. Partner

Be careful with sql injection.

PIVOT on Steroids

http://www.sqlmag.com/Articles/ArticleID/94268/pg/3/3.html

AMB

|||Thanks. So I guess pivot is not really used for this sort of thing.

|||

Nope, you are wrong.

He used the PIVOT operator, but he listed or collected all the property values on one variable and given those stored values to your PIVOT operator query...

Print @.SQL

You will get the query which is executed here..

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.

Cross-tab query in SQL Server

I have a table with results from several inspection tests. The Pass/Fail parameter in the cross-tab output would be Pass if all tests for that serial number are Pass, and Fail if any of the results for that serial number are Fail.

How would I create a cross-tab query in SQL Server?

Table for input to cross-tab query: [Inspection Data]

SerialNumber ParamName Result Pass/Fail

001 Test1 3.43 Pass

001 Test2 3.27 Pass

001 Test3 2.97 Pass

002 Test1 2.88 Pass

002 Test2 3.01 Pass

002 Test3 4.22 Fail

003 Test1 3.11 Pass

003 Test2 2.91 Pass

003 Test3 3.28 Pass

Cross-tab Query Output:

Test1 Test2 Test3 Pass/Fail

001 3.43 3.27 2.97 Pass

002 2.88 3.01 4.22 Fail

003 3.11 2.91 3.28 Pass

Thanks,

Sam

SELECT b.sno, b.test1, b.test2, b.test3, c.passfail FROM (select a.sNo
, min(case a.pName when 'test1' then result end) as [test1]
, min(case a.pName when 'test2' then result end) as [test2]
, min(case a.pName when 'test3' then result end) as [test3]
from dbo.pivotTable$ as a
group by a.sNo) b INNER JOIN (SELECT sNo, min(pOrf) AS PassFail
FROM pivotTable$ GROUP BY sNo) c ON c.sNo=b.sNo

For SQL Server 2005, you can:
SELECT p.sno, p.test1, p.test2, p.test3, b.PassFail
FROM (SELECT sno, pName, result
from pivotTable ) t
pivot (MAX(t.result) for t.pName in (test1,test2,test3)) AS p
INNER JOIN (SELECT sNo, min(pOrf) AS PassFail
FROM pivotTable$ GROUP BY sNo) b ON p.sNo=b.sNo|||

I am having trouble getting the syntax correct when I try to implement the real query. It tells me "Incorrect syntax near the keyword 'ON'

Do you have any idea what it might be?

select b.[Work Order No], b.[Serial No], b.[Apex], b.[Height]], b.[Radius], b.[Visual], b.[IL], b.[BR], c.[PF]

from ((select a.[Work Order No], a.[Serial No]

, min(case a.[Parameter Name] when 'Apex Offset' then [Value] end) as [Apex]

, min(case a.[Parameter Name] when 'Fiber Height ( Spherical Fit )' then [Value] end) as [Height]

, min(case a.[Parameter Name] when 'Radius of Curvature' then [Value] end) as [Radius]

, min(case a.[Parameter Name] when 'Visual Inspection' then [Value] end) as [Visual]

, min(case a.[Parameter Name] when 'Insertion Loss' then [Value] end) as [IL]

, min(case a.[Parameter Name] when 'Back Reflection' then [Value] end) as [BR]

from dbo.[Inspection Data] as a

group by a.[Work Order No],[Serial No] ) b inner join ((select [Work Order No], [Serial No], min([Pass/Fail]) as PF

from dbo.[Inspection Data] group by [Work Order No],[Serial No] ) c on c.[Work Order No]=b.[Work Order No] and c.[Serial No] = b.[Serial No]

Thank you,

Sam

|||

"...

group by a.[Work Order No],[Serial No] ) b inner join ((select [Work Order No], [Serial No], min([Pass/Fail]) as PF

from dbo.[Inspection Data] group by [Work Order No],[Serial No] ) c on c.[Work Order No]=b.[Work Order No] and c.[Serial No] = b.[Serial No]"

Is this an extra parathenses (in red)?

Crosstab Query

Hi All
I have a table as illistarted beloe that cotains three
mod types (x, y and z). How to I write a query that
will return the count of each mod type like a cross tab
ie.
x y z
4 2 3
CREATE TABLE ##Mods
(
ModType CHAR(1),
ModName VARCHAR(20)
)
INSERT INTO ##Mods VALUES ('X', 'CCC')
INSERT INTO ##Mods VALUES ('X', 'CCC')
INSERT INTO ##Mods VALUES ('X', 'CCC')
INSERT INTO ##Mods VALUES ('X', 'AAA')
INSERT INTO ##Mods VALUES ('Y', 'AAA')
INSERT INTO ##Mods VALUES ('Y', 'AAA')
INSERT INTO ##Mods VALUES ('Z', 'BBB')
INSERT INTO ##Mods VALUES ('Z', 'BBB')
INSERT INTO ##Mods VALUES ('Z', 'BBB')
Thanks in Advance.Hi
SELECT COUNT(CASE WHEN ModType='X' THEN 1 END)'X',
COUNT(CASE WHEN ModType='Y' THEN 1 END)'Y',
COUNT(CASE WHEN ModType='Z' THEN 1 END)'Z'
FROM ##Mods
<anonymous@.discussions.microsoft.com> wrote in message
news:14d5601c4156c$4b978910$a301280a@.phx
.gbl...
> Hi All
> I have a table as illistarted beloe that cotains three
> mod types (x, y and z). How to I write a query that
> will return the count of each mod type like a cross tab
> ie.
> x y z
> 4 2 3
>
> CREATE TABLE ##Mods
> (
> ModType CHAR(1),
> ModName VARCHAR(20)
> )
> INSERT INTO ##Mods VALUES ('X', 'CCC')
> INSERT INTO ##Mods VALUES ('X', 'CCC')
> INSERT INTO ##Mods VALUES ('X', 'CCC')
> INSERT INTO ##Mods VALUES ('X', 'AAA')
> INSERT INTO ##Mods VALUES ('Y', 'AAA')
> INSERT INTO ##Mods VALUES ('Y', 'AAA')
> INSERT INTO ##Mods VALUES ('Z', 'BBB')
> INSERT INTO ##Mods VALUES ('Z', 'BBB')
> INSERT INTO ##Mods VALUES ('Z', 'BBB')
> Thanks in Advance.

Crosstab in TSQL

Does anyone know of any code to produce a crosstab table or
temp table? I found several examples of stored procs to do this,
however they just produce results. I need to produce a table
to use to update another table.
Thanks much,
Marc MillerA simple one
USE pubs
GO
SELECT
stor_id,
SUM(CASE YEAR(ord_date)
WHEN 1992 THEN qty
ELSE 0
END) AS c1992,
SUM(CASE YEAR(ord_date)
WHEN 1993 THEN qty
ELSE 0
END) AS c1993,
SUM(CASE YEAR(ord_date)
WHEN 1994 THEN qty
ELSE 0
END) AS c1994
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||This article describes various techniques for implementing a crosstab type
query. Basically, it's just a group by query.
http://www.aspfaq.com/show.asp?id=2462
The result of any query can be output to a table or temporary table:
the SELECT.. INTO.. syntax will create a new table with the same column and
data type structure as the query result.
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
into
mycrosstab
from
mytable
group by
a,
b
the INSERT INTO.. SELECT.. syntax will insert the query result into an
existing table:
insert into mycrosstab
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
from
mytable
group by
a,
b
For temporary tables, just prefix the insert table with a # symbol.
insert into #mycrosstab . . .
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||Uri,
Thanks! I took your suggestion a step further, becuase I the field names
can be variable. Here's what I did
(just for posterity I suppose, and anyone else trying to do the same.)
Temp table ##c_bt:
CUST_ACCT COLUMN_NAME AMOUNT
0123456 January 500.00
5464646 March 53.00
5161616 January 333.33
0123456 May 500.00
5464646 June 53.00
5161616 June 333.33
etc.
CODE:
DECLARE @.col varchar(20)
DECLARE @.DDL varchar(1000)
DECLARE curcols CURSOR
FOR select distinct column_name from ##c_bt order by column_name
OPEN curcols
SET @.DDL = 'Select cust_acct, '
FETCH NEXT FROM curcols INTO @.col
WHILE (@.@.fetch_status <> -1)
BEGIN
set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
amount ELSE 0 END) AS ' + rtrim(@.col) + ','
FETCH NEXT FROM curcols INTO @.col
END
CLOSE curcols
DEALLOCATE curcols
SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
EXEC(@.DDL)
Thanks again,
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>A simple one
> USE pubs
> GO
> SELECT
> stor_id,
> SUM(CASE YEAR(ord_date)
> WHEN 1992 THEN qty
> ELSE 0
> END) AS c1992,
> SUM(CASE YEAR(ord_date)
> WHEN 1993 THEN qty
> ELSE 0
> END) AS c1993,
> SUM(CASE YEAR(ord_date)
> WHEN 1994 THEN qty
> ELSE 0
> END) AS c1994
> FROM Sales
> GROUP BY stor_id
> ORDER BY stor_id
>
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
>|||Marc
Two questions I'd liek to ask you.
1) Why do you need to use a global temporary table?
2) Why do you need to use a cursor?
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
> Uri,
> Thanks! I took your suggestion a step further, becuase I the field names
> can be variable. Here's what I did
> (just for posterity I suppose, and anyone else trying to do the same.)
> Temp table ##c_bt:
> CUST_ACCT COLUMN_NAME AMOUNT
> 0123456 January 500.00
> 5464646 March 53.00
> 5161616 January 333.33
> 0123456 May 500.00
> 5464646 June
> 53.00
> 5161616 June 333.33
> etc.
> CODE:
> DECLARE @.col varchar(20)
> DECLARE @.DDL varchar(1000)
> DECLARE curcols CURSOR
> FOR select distinct column_name from ##c_bt order by column_name
> OPEN curcols
> SET @.DDL = 'Select cust_acct, '
> FETCH NEXT FROM curcols INTO @.col
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
> amount ELSE 0 END) AS ' + rtrim(@.col) + ','
> FETCH NEXT FROM curcols INTO @.col
> END
> CLOSE curcols
> DEALLOCATE curcols
> SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
> SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
> EXEC(@.DDL)
> Thanks again,
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>|||Uri,
1) The temp table need not be global, it was just the way I was
experimenting, but I do need a table
to join and update another table.
2) I'm using the cursor to loop through its results to discover the 'column
names' that I need to build
my SQL. The column names can differ from month to month for me,
depending on the source data.
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
> Marc
> Two questions I'd liek to ask you.
> 1) Why do you need to use a global temporary table?
> 2) Why do you need to use a cursor?
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
>|||Marc
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
I don't see any reasons to use a global temrorary table. Pls refer to the
BOL for more details

> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
>
Search on internet "dynamic crosstab" written by Itzik Ben-Gan
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uvNydJ0JGHA.2864@.TK2MSFTNGP10.phx.gbl...
> Uri,
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
>|||Check out the RAC utility for pivoting/xtabs,
no coding necessary
www.rac4sql.net|||Is there an easy way, in outlook express, to stop this guy's spam from
showing up based on their alias?
I am sure that others use noname@.noname.com just to avoid being put on
mailing lists, and I don't want to filter out useful messages inadvertantly.
Thanks,
Jim
"05ponyGT" <noname@.noname.com> wrote in message
news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
> Check out the RAC utility for pivoting/xtabs,
> no coding necessary
> www.rac4sql.net
>|||Is that better:)
Have a nice day.
www.rac4sql.net
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:epx%23Ir1JGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Is there an easy way, in outlook express, to stop this guy's spam from
> showing up based on their alias?
> I am sure that others use noname@.noname.com just to avoid being put on
> mailing lists, and I don't want to filter out useful messages
> inadvertantly.
> Thanks,
> Jim
> "05ponyGT" <noname@.noname.com> wrote in message
> news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
>