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?
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;
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
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~
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.
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
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
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?
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?
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.
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.
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?
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.
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
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.
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.
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...
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?
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 .... .... ....
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.
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?
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.
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!
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
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....
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)
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.
