Normalizing A Crosstab
Aug 24, 2006
I re-designed a predecessor's database so that it is more properly
normalized. Now, I must migrate the data from the legacy system into
the new one. The problem is that one of the tables is a CROSSTAB
TABLE. Yes, the actual table is laid out in a cross-tabular fashion.
What is a good approach for moving that data into normalized tables?
This is the original table:
CREATE TABLE [dbo].[Sensitivities](
[Lab ID#] [int] NULL,
[Organism name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[Source] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BACITRACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CEPHALOTHIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CHLORAMPHENICOL] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[CLINDAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ERYTHROMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[SULFISOXAZOLE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NEOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OXACILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TETRACYCLINE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[TOBRAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VANCOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TRIMETHOPRIM] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[CIPROFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[AMIKACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AMPICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CARBENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[CEFTAZIDIME] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GENTAMICIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[POLYMYXIN B] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MOXIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[GATIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[SENSI NOTE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
View 5 Replies
ADVERTISEMENT
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
Nov 8, 2006
Please i have created some tables Delivary with this columns (DelivaryId,DelivaryNo,QtyRecieved,DelivaryDate,ProductId) and Product with this columns (ProductId,ProductCode,ProductName,ProductPrice) as you can see the product table keeps record of products whlie the delivary table keeps record of stock supplied. I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table
Please help
View 6 Replies
View Related
Aug 10, 2005
So I'm creating an administrative back end for a site that's already been created, and whoever made the tables the site uses didn't know much about database design. So I need to normalize this table of Links so it can be easier to have someone make changes and updates to it, but then I need to put all my normalized tables back together to create a View exactly like the old table which the old site can select from. Basically the stipulation is I can't change the code for the old site so I have to make it think it's still selecting from the same table with the same type of parameters. Is it worth doing all this? Or should I just tough it out with this really ugly table?Here's the table: and here's the site that uses this table:http://waahp.byu.edu/links.aspThanks!~Cattrah~
View 3 Replies
View Related
Aug 28, 2006
Hi
Please can someone point me in the direction, i built a very badly designed database consisting of only one huge table when i first started databases, since learning about normalization i have designed and set up a new database which consists of many more tables instead of just the one. My question is where do i start in transfering the data from the old single tabled database to my new multi-tabled database?
I have MS SQL server 2005 managment studio if that helps, but want to transfer around 200,000 rows of data into the new database. Both new and old databases are on the same server.
thanks in advance
View 11 Replies
View Related
Feb 24, 2004
I am a beginner, so please bare with me. I get very confused on how to normalize my database.
Firstly: The employees in the company I work for are in various departments and can have more then one title and work in more then one department.
Example: John Smith can work in the engineering department as a detailer and an engineer and at the same time work as a project manager for the management department.
How do I setup this table structure?
Employees Table
Login (PK) | First | Last | Extension.......
---------------------------------------------
jsmith | John | Smith | 280
Department Title Breakdown
Department | Title
--------------------------
Engineering | Detailer
Engineering | Engineer
Management | ProjectManager
Job Description
Login | Title
-------------------------
jsmith | Engineer
jsmith | Detailer
jsmith | ProjectManager
This is important to break this down because for each project the following is saved:
Project Listing
Project | Detailer | Estimator | Sales | Engineer |....... | Location
10001 | jsmith | jdoe | mslick | sjunk | ...... | Las Vegas
Or should the project be broken down as well
Project Listing
Project | Location
10001 | Las Vegas
Project Team
Project | Member | Activity
10001 | jsmith | Engineer
10001 | mstevens | Detailer
Any thoughts on how to normalize this?
Mike B
View 6 Replies
View Related
Jul 13, 2006
I have this table...CREATE TABLE #Test (ID char(1), Seq int, Ch char(1))INSERT #Test SELECT 'A',1,'A'INSERT #Test SELECT 'A',2,'B'INSERT #Test SELECT 'A',3,'C'INSERT #Test SELECT 'B',1,'D'INSERT #Test SELECT 'B',2,'E'INSERT #Test SELECT 'B',3,'F'INSERT #Test SELECT 'B',4,'G'....and am searching for this query....SELECT ID, Pattern=...?? FROM #Test....??....to give this result, where Pattern is the ordered concatenation ofCh for each ID:ID PatternA ABCB DEFGThanks for any help!Jim
View 2 Replies
View Related
Jun 9, 2006
Hi,
I have a table like this:
Col1 First_Year Last_Year
a 1990 1993
I want this data to be converted to
a 1990
a 1991
a 1992
a 1993
Is there any simple way to do it in SSIS without using Script Component?
Thx
View 3 Replies
View Related
Dec 27, 2003
THE LAYOUT:
I have two tables: "Applicant_T" and "StreetSuffix_T"
The "Applicant_T" table contains fields for the applicant's current address, previous address and employer address. Each address is broken up into parts (i.e., street number, street name, street suffix, etc.). For this discussion, I will focus on the street suffix. For each of the addresses, I have a street suffix field as follows:
[Applicant_T]
CurrSuffix
PrevSuffix
EmpSuffix
The "StreetSuffix_T" table contains the postal service approved street suffix names. There are two fields as follows:
[StreetSuffix_T]
SuffixID <-----this is the primary key
Name
For each of the addresses in the Applicant_T table, I input the SuffixID of the StreetSuffix_T table.
THE PROBLEM:
I have never created a view that would require the primary key of one table to be associated with multiple fields of another table (i.e., SuffixID-->CurrSuffix, SuffixID-->PrevSuffix, SuffixID-->EmpSuffix). I want to create a view of the Applicant_T table that will show the suffix name from the StreetSuffix_T table for each of the suffix fields in the Applicant_T table. How is this done?
View 6 Replies
View Related
Nov 7, 2005
I have a view with patient data. It looks like below
patid date pulmdc pulmstatus endodc endostatus
100 4/1/05 10 Good null null
100 5/1/05 10 Good 12 Poor
I want to create sql which by each patient, by date, by these four fields ,get
patid 4/1/05 pulmdc-10 pulmstatus-good
patid 5/1/05 pulmdc-1 pulmstatus-good
patid 5/1/05 endodc-12 endostatus-poor
View 1 Replies
View Related
Apr 27, 2006
Greetings all,
I have created an SSIS package that takes data from a very large table (301 columns) and puts it in a new database in smaller tables. I am using views to control what data goes to the new tables. I also specified that it drop the destination table and recreate it prior to copying the data. The reason for this is so that old data removed from the larger database will get removed from the normalized databases.
I have 2 things I am trying to figure out..
1. I would like to have the package set a specific row in each new table to be the primary key (this will allow us to use relationships when querying the data).
2. I decided I wanted to sort the data as it copies. I am using the BI Visual Studio for my editing. In the Data Flow view I cannot seem to disconnect the output from the Source block so I can connect it to the Sort block and then feed that to the output block. What am I missing here?
Thanks
View 7 Replies
View Related
Jan 23, 2006
Hi!
I have a table with the following columns:
account_nr, account_totaling_members, account_type
the account_totaling_members column contains a pipe sperated list of accounts in a varchar: "1001|1002|1003"
I need to normalize this so that i get records like:
"10", "1001", "sum"
"10", "1001", "sum"
"10", "1002", "sum"
..and so forth
Does anyone have any idea how to accomplish this?
View 3 Replies
View Related
Jun 21, 2006
I am copying data from one denormalized table to a COUPLE of normalized ones.
I am using multicast, following advices from the forum.
The problem I have is that the two destination tables (A and B) are sharing a foreign key relationship.Filling in A is no problem, but when I want to fill in B, I don't know how to populate its foreign key, since the multicast doesn't know the corresponding primary key in table A.
View 9 Replies
View Related
Aug 20, 2015
CREATE TABLE CATEGORIES(CATEGORYID VARCHAR(10), CATEGORYLIST VARCHAR(200))
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1000', 'S01:S03, S09:S20, S22:S24')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1001', 'S11:S12')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1002', 'S30:S32, S34:S35, S60')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1003', 'S40')
The CATEGORYLIST strings are composed of value ranges separated by a colon (:) and multiple value ranges separated by a comma.
The results I need are:
CATEGORYID STARTRANGE ENDRANGE
1000 S01 S03
1000 S09 S20
1000 S22 S24
1001 S11 S12
1002 S30 S32
1002 S34 S35
1002 S60 S60
1003 S40 S40
I have tried taking the original data and parsing it out as an XML file. Is there a less cumbersome way to do this in TSQL?
View 1 Replies
View Related
Nov 6, 2007
I'm new to SSIS and have run into a problem I'm hoping someone can help me with.
Basically, I have a flat file that looks something like:
ID,Type,Description,Results
1,Test1,This is a test,5
2,Test1,This is also a 1 test,7
3,Test1,This is also a 1 test,13
4,Test2,This is a second test,14
5,Test2,This is also a second test,18
I'm trying to normalize the data by extracting out individual rows that have the same "Type" column value. So what I want is to extract each unique type and description into a separate table. This would give me two new rows, one for a type of Test1, and one for a type of Test2, with the descriptions. Does this make sense? Then I could relate the individual results to these test types. In my scenario, I don't care which description is used; I just want to take the first description that shows up with the associated "Type."
Does anyone have any idea of how I could go about doing this? I could pull out all unique "Types" from the rows with the Aggregate transformation, but I'm trying to figure out how to get the description that goes along with it.
Thanks,
Brian
View 1 Replies
View Related
May 22, 2015
I have a large data set with 10s of millions of rows of contact information. The data is in CSV format and contains 48 columns of information (First name, MI, last name, 4 part address, 10+ demographic points, etc.) and I'm struggling with how I should design the database and normalize this data, or if I should normalize this data.
My 2 thoughts for design were either:
Break the columns into logical categorical tables (i.e. BasicContactInfo, Demographics, Financials, Interests, etc.) Keep the entire row in one table, and pull out the "Objects" into another table (i.e. ContactInformation, States, ZIPCodes, EmployementStatus, EthnicityCodes, etc.)
The data will be immutable for the most part, and when I get new data, I'll just create a new database and replace the old one.
The reason I like option 1 is because it makes importing easier, since I can just insert the appropriate columns from each row into the appropriate tables. Option number 2 feels like it would be faster to get metrics on the data, like how many contacts live in which states, or what is the total number of unique occupations in the data set. Plus I'll be able to make relationships between the tables, like which state is tied to which zipcode, which city is tied with which county, etc. Importing that data might be more tricky, since I don't think SQL Bulk Copy will allow for inserting into normalized tables like that.
The primary use for this data is to allow our sales force to create custom lists of contact information based on a faceted search page. The sales person would create the filter, and then I will provide them with the resulting data so they can start making business contacts. Search performance needs to be good. Insert, update, and deletes won't happen once the data has been imported.
What should I look for in designing this database? Any good articles on designing tables around wide data sets like my contact information?
View 6 Replies
View Related
Oct 17, 2012
I need to normalise comma separated strings of tags (SQL Server 2008 R2).
E.g. (1, 'abc, DEF, xyzrpt') should become
(1, 'abc')
(1, 'DEF')
(1, 'xyzrpt')
I have written a procedure in T-SQL that can handle this. But it is slow and it would be better if the solution was available as a view, even a slow view would be better.
Most solutions I found go the way round: from (1, 'abc'), (1, 'DEF') and (1, 'xyzrpt'), generate (1, 'abc, DEF, xyzrpt').
If memory serves, it used "FOR XML PATH". But it's been a while and I may be totally wrong.
View 2 Replies
View Related
May 28, 2015
I have a script that resolve's data into xml like this, ex:
<root>
<title>A</title>
<id>1</id>
<nodes>
<node>
<id>2</id>
<title>A.1</title>
</node>
</nodes>
</root>
And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.
For the root I am using
FOR XML PATH('root'),TYPE; and for the hierarchy that follows
FOR XML RAW ('node'), root('nodes'), ELEMENTS
View 0 Replies
View Related
Aug 21, 2015
I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
View 3 Replies
View Related
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
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
Aug 23, 2005
Hi Guys!Is there anything like cross tab of access in sql server?Thanks.
View 1 Replies
View Related
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
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
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
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
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
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
Mar 8, 2006
Is there a SQL version of a Crosstab Query??
View 14 Replies
View Related
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
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
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
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
