SQL Crosstab Results In Numbers On The 'diagonal'

Jul 23, 2005

Hello --

We have annual values for several 'MeasName':
Capital expenditure increment
Growth rate
Subscribers

The table has these fields:
Year
MeasName
MeasValue

We want the result of the crosstab to look like:

MeasName 2005 2006 2007 2008 2009
-----------------------------------------------------------------
CapexIncrement 33 33 41 41 41
GrowthRate 0 .1 .1 .1 .1
Subscribers 42000 46000 50000 55000 60000

The code below results in:
CapexIncremt 33 0 0 0 0
CapexIncremt 0 33 0 0 0
CapexIncremt 0 0 41 0 0
CapexIncremt 0 0 0 41 0
CapexIncremt 0 0 0 0 41
GrowthRate 0 0.1 0 0 0
GrowthRate 0 0 0.1 0 0
GrowthRate 0 0 0 0.1 0
GrowthRate 0 0 0 0 0.1
Subscribers 42000 0 0 0 0
Subscribers 0 46000 0 0 0
Subscribers 0 0 50000 0 0
Subscribers 0 0 0 55000 0
Subscribers 0 0 0 0 60000


SELECT MeasName,
SUM(CASE Yr WHEN 2005 THEN MeasValue ELSE 0 END) AS '2005',
SUM(CASE Yr WHEN 2006 THEN MeasValue ELSE 0 END) AS '2006',
SUM(CASE Yr WHEN 2007 THEN MeasValue ELSE 0 END) AS '2007',
SUM(CASE Yr WHEN 2008 THEN MeasValue ELSE 0 END) AS '2008',
SUM(CASE Yr WHEN 2009 THEN MeasValue ELSE 0 END) AS '2009'
FROM MetricsTime
GROUP BY Yr, MeasName

Can anyone tell me how to change the code to result in the layout we
want?

Thanks for any help.

Larry Mehl

View 1 Replies


ADVERTISEMENT

Crosstab Results Advice

Mar 22, 2006

I've got the query below giving me the results I need (results 1) but I need the results to be one row of data by person with the first column (sessionNbr) combined into one column per person (results 2). (I left out some of the columns in the results examples for brevity's sake.) This will be a rarely executed query and only contains a total record count return of less than 5,000 rows. What is the best method of achieving this in your opinion? Thanks to all for any help....

query:
select left(s.session_name,charIndex(' ',s.session_name)-1) as sessionNbr, rs2.firstname, rs2.lastname, rs2.organization_name, rs2.address, rs2.city_name, rs2.state_initials, rs2.zip, rs2.event_fk, rs2.person_pk from ncahec.dbo.tblSession s,
(select 'XXX-XX-' + right(person_id,4) as ssn, y.firstname, y.lastname, isNull(o.organization_name,'') as organization_name, isNull(y.home_address1,'') + ' ' + isNull(y.home_address2,'') as address, isNull(y.home_zip,'') as zip, c.city_name, t.state_initials, rs1.event_fk, y.person_pk from tblPerson y left join ncahec.dbo.tblAffiliation a on a.affiliation_pk=y.primary_affiliation_fk left join tblOrganization o on o.organization_pk=a.organization_fk left join tblCity c on y.home_city_fk=c.city_pk left join tblState t on c.state_fk=t.state_pk,
(select r.event_fk, r.person_fk, registration_pk from tblRegistration r where (r.registration_status_fk=1) and (r.event_fk=11266) ) as rs1 where rs1.person_fk=y.person_pk)
as rs2 where (s.session_status_fk=1) and (rs2.event_fk=s.event_fk) and (left(s.session_name,1) in ('0','1','2','3','4','5','6','7','8','9'))
order by rs2.person_pk,
case when charIndex('~',left(s.session_name,10))>0 then 1 when charIndex('-',left(s.session_name,10))>0 then 2 end,
left(s.session_name,charIndex(' ',s.session_name)-1)

Results 1
sessionNbr, firstName, lastName, person_pk, event_fk
2JohnSmith11266409
5JohnSmith11266409
6JohnSmith11266409
15JohnSmith11266409
4Jane Doe11266528
8Jane Doe11266528
9Jane Doe11266528
19Jane Doe11266528
20Jane Doe11266528

Results 2
sessionNbrs, firstName, lastName, person_pkevent_fk
2, 5, 6, 15JohnSmith11266409
4, 8, 9, 19, 20Jane Doe11266528

View 1 Replies View Related

User Defined Functions, Passing Parameters From Another Udf's Results (end Result=Crosstab)

Oct 25, 2005

Hi All:I've read a whole slew of posts about creating temp tables using storedproceedures to get the crosstab ability, but I'm wondering if, for thisspecific case, there might be a more efficient way.What makes this question different from the others that I've read isthat I'm using user defined functions, not tables. I actually thinkthat I've got the crosstab thing down, it's just passing the parameterto the 2nd udf that's messing me up.I've got a people table and an address table. Each person can havemultiple addresses. I need to create a dataset that has in each rowthe name of the person, the first address, any second address, and anythird address. I only need to show the first 3, so if there's 100, Ican just ignore the rest.I created a user defined function to return the 1st, 2nd, or 3rdaddress for a given person.udf_ReturnAddress(PersonID,MatchNumber)Another user defined function returns the people that I'm looking for(potential duplicates for a person in this case).udf_ReturnPossibleDupsForAPerson(PersonID)SELECTMain.FoundPersonID, Main.LastName, A1.Street, A2.Street,A3.StreetFROMudf(ReturnPossibleDupsForAPerson(@PersonID) MainTableCROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,1) Adr1) A1CROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,2) Adr2) A2CROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,3) Add3) A3If, for the first parameter for the return address function, I replaceMain.FoundPersonID with the ID of a person, it works just fine. Iobviously don't want a static id as a parameter - I want to use the IDof the person that the first udf found. Leaving the variableMainTable.PersonID there causes an error in the query designer though.I get "Error in list of function arguments: '.' not recognized.So maybe my problem is that I just don't know how to pass the id of theperson that's found by the first UDF as the parameter of the functionto find the found person's 3 addresses.Any guidance would be greatly appreciated!ThanksKen

View 6 Replies View Related

How To Put Serial Numbers For Results In Group

Apr 4, 2012

I need to put the serial numbers for results in group in SQL Server 2000. Please see below:

My input:
procedureid procname
1 A
1 B
2 A
2 B
2 C
2 D
3 A
3 B
3 C

Output I need:
procedureid procname serial_num
1 A 1
1 B 2
2 A 1
2 B 2
2 C 3
2 D 4
3 A 1
3 B 2
3 C 3

Here is my table:

create table po(
procedureid int,
procname varchar(10),
)
insert into po values (1,'A')
insert into po values (1,'B')

[Code] ....

View 11 Replies View Related

Applying A Diagonal Line In A Report

Feb 21, 2007

My client cannot workout how to apply diagonally aligned text on a report.

We need to apply the text on statements and invoices to alert the clients to accelerate their payment, or notify them of account issues.

Could someone please tell me where I could find the information required to do this so I can forward it to the client.

View 1 Replies View Related

SQL Server 2012 :: How To Compare List Of Numbers Kind Of Like Lottery Results

Feb 5, 2015

Say you have a table that has records with numbers sort of like lottery winning numbers, say:

TableWinners
num1, num2, num3, num4, num5, num6
33 52 47 23 17 28
... more records with similar structure.

Then you have another table with chosen numbers, same structure as above, TableGuesses.

How could you do the following comparisons between TableGuesses and TableWinners:

1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).

2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.

View 8 Replies View Related

SQL Server 2012 :: List Of Order Numbers Based On Stock Availability - Filter Results?

Dec 23, 2014

Trying to build a list of order numbers based on stock availability.

The data looks something like this:

OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10

In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.

View 1 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



View 1 Replies View Related

Access Crosstab -&> SQL Crosstab

Jan 9, 2006

Hi all
I'm transferring some Access queries to SQL server and the crosstabs don't want to work, can anyone shed any light on the query below.

Thanks.

TRANSFORM Min(tCompany.cCompanyName) AS CompanyName
SELECT tProjContacts.ProjectID
FROM tCompany INNER JOIN (tProjContacts INNER JOIN tCompanyType ON tProjContacts.CoTypeId = tCompanyType.CoTypeId) ON tCompany.CompanyID = tProjContacts.CompanyID
WHERE (((tCompanyType.CoType) Like "*topo*" Or (tCompanyType.CoType) Like "*ground*"))
GROUP BY tProjContacts.ProjectID
PIVOT tCompanyType.CoType;

View 1 Replies View Related

Query Analyzer Shows Negative Numbers As Positive Numbers

Jul 20, 2005

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View 7 Replies View Related

I Need To Update A Table With Random Numbers Or Sequential Numbers

Mar 11, 2008



I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.

Thanks for any help

View 6 Replies View Related

Generate List Of All Numbers (numbers Not In Use)

Feb 21, 2007

I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:

select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)


but don't know how. Any clues?

View 1 Replies View Related

Dataflow To Excel - Convert Numbers Stored As Text To Numbers Excel Cell Error

Mar 27, 2007

I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:@; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.


So close.. Thanks for any help or information.

View 1 Replies View Related

DTS & Crosstab

Apr 19, 2000

I am a neewbie to SQL 7 and having previously used Access for some time.. I have a table with a date field in a want to create a table with the values filling columns in another table by month ie April,may ,Jun and so on. Ia m using the Month function to get the month number but I dont know how to crosstab this into the relevant columns in the new table...I thinks DTS is the way and do a transformation on the month field but am struglling at the mo...Any help greatfully appreciated...

Regards

Andrew Wall

View 2 Replies View Related

Please Help Me In Crosstab

Jun 18, 2007

Hi i am new to crosstab feature.i want a crosstab dynamically with this below table please help me .Thanks in advance.

Table :-

idnamecosttypemonth&yearamount

1a1Bcost9/2005300
2a1Ecost9/2005200
3a1Acost9/2005100
4a2Bcost10/2005150
5a2ECost10/2005100
6a2Acost10/2005150
7a3Bcost11/2005150
8a3ECost11/2005100
9a3Acost11/2005150



I want result table like this :-

ID Name Cost_Type 9/2005 10/2005 11/2005

1 a1Bcost 300 -
2 a1Ecost 200 -
3 a1Acost 100 -
4 a2Bcost -150
5 a2ECost - 100
6 a2Acost - 150
7 a3Bcost - -150
8 a3ECost - -100
9 a3Acost - -150


I want above result Please Give me your help.

Thanks in advance,
Rajeev

View 1 Replies View Related

CrossTab

Aug 23, 2005

Hi Guys!Is there anything like cross tab of access in sql server?Thanks.

View 1 Replies View Related

Crosstab Help

Jul 13, 2007

Hello,



I need to accomplish the turning column data into row data via SQL. I can sorta get what I want with creating the report as a matrix report. However, I always seem to need one little thing to happen to shape my data as I need it. Anyway, here goes...



I have a table with 4 columns



UNIQUE | Code | FieldID | CustomField



The Code column is the customer code. FieldID numbers 1-100 and CustomField has string data.



My problem. I need to be able to choose ALL Customers [Code] where FieldID/s ="6", "7" & "8" and the CustomField rowdata corresponding to the FieldID data. BUT I then need to be able to use a daterange parameter on any row with a FieldID of "6" but that is not a datetime format it is in a string format.



Currently my SQL is:



SELECT
[Unique ID], Code, [Field ID] AS IUdate, [Field ID] AS IUNote, [Field ID] AS IUReq, [Custom Field]

FROM
dbo.[Customer Custom Field]

WHERE
(Code = '07-8111')AND ([Field ID] = 6) OR (Code = '07-8111')AND ([Field ID] = 7) OR (Code =

'07-8111')AND ([Field ID] = 8)



However, everytime I try to run a daterange against, I get all kinds of data I don't need because the parameter is running against all the fields and NOT just the stringdate.



My idea is to convert the column data into row data (like a crosstab query) to sharpen it up for parameter ranges.



Is there something i am missing here? Can someone point me in the right direction?



Thanks in advance!



phorest

View 6 Replies View Related

Crosstab Query

Mar 14, 2002

Hello everyone,

I have a tabel which looks like the follows:

CODE Type Number
1 account 20
1 empl 3
2 account 15
2 empl 6
3 account 32
3 empl 7

I need to show the results like

CODE Account Empl
1 20 3
2 15 6
3 32 7

I've tried different attempts but so far I always get 2 rows for each code,where one column shows a NULL value.
How do I have to do this ?

View 1 Replies View Related

Crosstab Query

Apr 5, 2001

Is there a way to write Crosstab query in SQL 7.0.

I have a Table which has partner, usernames and the city they are from, apart from other fields. I have another table which tracks the user activity on a day by number of visits to the web site. What i want is like this :-

Date Total Visits Atlanta Connecticut .... .... ....

03/01/2001 5025 567 324 .... .... ....
03/02/2001 6789 423 146 .... .... ....
... ... ... ... .... .... ....

I.E. I wan't the city names to appear as columns. The main catch here is that this data is for a given partner. So the city names could be different for different users from different partners. i.e. city names have to be generated on the fly by looking at the partner table and then by getting all its users and seeing what all are the cities involved.

Any help would be appreciated.

Thanks
Sumit.

View 1 Replies View Related

Crosstab Reports

Jun 4, 2001

I am trying to run a report off a crosstab query. The report calls for columns A - F, which are returned from the crosstab query. The only problem is that at times some of the columns are not returned by the query because there is no data associated with them. The report asks for column X, and no such columns exists in the query result; thus, an error is generated. My question is: how do I deal with this problem? I would like to avoid having to build the report from scratch. Is there some way that at runtime I could programatically tell the report to ignore these fields?

View 2 Replies View Related

Crosstab Problem

Nov 10, 2005

I have a table called Ideas and a table called Users_Ideas. In Users_Ideas, there can be up to four rows referencing Ideas with a foriegn key. I need to select all rows from Ideas and for each Idea display the users associated with it.

View 8 Replies View Related

Yet Another Crosstab Problem

Nov 14, 2005

Hi all,
i see there are many posts on crosstab queries in this forum but i can't seem to find a solution to my problem. hope you can help me.

I have a view of server crashes:

[dirty shutdown] [previous clean] [server name]
2005-10-01 2005-09-01 srv1
2005-11-01 2005-10-10 srv2

which displays the date of a dirty shutdown of a server, and the date of the previous clean shutdown of this server plus the server name

Also, i have a view of alerts per server:

[alert id] [server name] [alert date] [alert name] [repeat count]
123 srv1 2005-09-05 an alert 0
124 srv1 2005-09-10 another alert 1
125 srv1 2005-09-20 an alert 0
126 srv2 2005-10-20 something else 0

If [repeat count] is 0, the alert was given once, if it is 1, there were 2 alerts etc.

Now, what i want (well not me but the guy i work for) is a view that displays all alerts between the clean and the dirty shutdown, per server:

[dirty] [clean] server total "an alert" "another alert" "something else"

2005-10-01 2005-09-01 srv1 4 2 2 0
2005-11-01 2005-10-10 srv2 1 0 0 1

Total is the total number of alerts for the server between the two dates. Of course, there are many servers, and the number of alert names varies over time which means i can't use "case when...".

Is this possible??? Would be extremely thankful for any help!

Regards,
Elisabet

View 1 Replies View Related

CrossTab Query

Mar 8, 2006

Is there a SQL version of a Crosstab Query??

View 14 Replies View Related

No Crosstab Like Access?

Mar 30, 2006

I'm trying to port a dymanically renderred form from a .mdb to a .adp. In the Access .mdb. The form is a representation of a crosstab query with unknown column headings (hence, the need for the form to be dynamically rendered at runtime).

I tried to port the crosstab from the .mdb to a stored procedure, but SQL Server doesn't like TRANSFORM, and perhaps the PIVOT as well. How do you do this in sql server?:

TRANSFORM Count(d.CAR_INIT) AS CountOfCAR_INIT
SELECT b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR
FROM ((TSA_HS_MPCT_CNT a INNER JOIN TSA_HS_COMB2 b ON a.RECC_COMB_ID = b.COMB_ID)
INNER JOIN TSA_HS_WKLD c ON b.WKLD_ID = c.WKLD_ID) INNER JOIN TSA_HS_OBJ_TRN d ON c.WKLD_ID = d.WKLD_ID
GROUP BY b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR
PIVOT d.LST_HMP_DTM + d.OBJ_DEP_TRN

Thanks,
Carl

View 1 Replies View Related

Crosstab Problem Help

Sep 20, 2006

Hi,
I have tried with the solutions available in the links provided by Pootle,but still I think I have to go a long way.My main problem is with the second aggregate column of previous year.
Let me put it again....


I have a table policy :

CREATE TABLE Policy(
Policyno VARCHAR(20)
,Inceptiondate DATETIME
,agentid VARCHAR(20))



I want a report like this (dates in DD/MM/YYYY)

date 32001 32002 32003 32004 2006 2005
01/08/2006 3 1 11 1 16 12
02/08/2006 1 1 1 2 5 22
03/08/2006 1 1 1 1 4 3



Now the problem is I can get a report using crosstab like this in crystal report

| agentid
-----------------
date | sum(policyno)
-----------------

which is giving a result like this

date 32001 32002 32003 32004 2006/ToTal
01/08/2006 3 1 11 1 16
02/08/2006 1 1 1 2 5
03/08/2006 1 1 1 1 4
Total 4 3 13 4 25


But I can't bring the previous year aggregate column ,i.e 2005 in the above case.

So can you suggest me a way or direction to acheive that?

Any help would be really appreciated.
BTW,Pootle gave these links in my previous post...
link1 (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21820764.html#16500817)
Link2 (http://www.sqlteam.com/item.asp?ItemID=2955)

View 3 Replies View Related

Crosstab Select!!

Apr 3, 2008

Hi
I have table called MyTable that has 3 columns (City , Brand, Price)
I want to select from this table in Cross Tab format
i.e.
My table has
CityBrandPrice
LondonDELL1227
LondonToshiba1100
LondonAcer1007
LondonHP1467
LondonIBM1193
SydneyToshiba2100
SydneyAcer2219
SydneyApple2589
SydneyVAIO2122
SydneyHP1929
SydneyIBM2877
TokyoToshiba7200
TokyoAcer5299
TokyoCOMPAQ9200
TokyoIBM8779
TokyoHP6286
ParisDELL1670
ParisApple1825
ParisVAIO1267
ParisHP1882
ParisCOMPAQ1636
ParisIBM1332
NewYorkDELL2000
NewYorkToshiba1288
NewYorkAcer2333
NewYorkApple2299
NewYorkVAIO2327

__________________________________

i want the select statment result to be like this
DELLToshibaAcerHPIBMAppleVAIOCOMPAQ
London12271100100714671193N/AN/AN/A
SydneyN/A2100221919292877N/AN/AN/A
TokyoN/A7200529962868779N/AN/A9200
Paris1670N/AN/A18821332182512671636
NewYork200012882333N/AN/A22992327N/A


City & Brand can be anything so i will not be able to hard code them in my Select.

who can help with that?!

View 2 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 1 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 1 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 1 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 3 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 1 Replies View Related

CrossTab Query

Sep 17, 2007

Hi,

Currently working on a Attendance System project .
Iam storing the data in table against the employee code, date and status (basically a rowwise data).

Would require a query which can generate a cross-tab display with Employee Codes (on X axis), Dates (on Y-Axis) and with the Attendance Status.

The sample data for the same is as follows:

EmpCode Att_Date Att_Status
------- -------- ----------
001 01/01/2007 P
001 01/02/2007 A
.. ... ..
001 01/31/2007 P

002 01/01/2007 P
. . .
. . .
. . .

Would require the output as..

EmpCode 01/01/2007 01/02/2007 ...... 01/31/2007
------- ---------- ---------- ----------
001 P A P
002 A P P
. . . .
. . . .
. . . .

Thanking you in anticipation.

Jabez.

View 10 Replies View Related

Crosstab Query

Jul 23, 2005

Can someone show me how to write a query to convert this:Year, Account, Qtr1, Qtr2, Qtr3, Qtr42004, 12345, 100, 200, 300, 4002005, 23456, 200, 300, 400, 500to this:Year, Account, Quarters, Amount2004, 12345, Qtr1, 1002004, 12345, Qtr2, 2002004, 12345, Qtr3, 3002004, 12345, Qtr4, 4002005, 23456, Qtr1, 2002005, 23456, Qtr2, 3002005, 23456, Qtr3, 4002005, 23456, Qtr4, 500So far I've only found examples which convert values to columns but notfor columns to values. TIA... AL

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved