i have the following Problem: i need to have a database-design in which there are a variable number of customers , a variable numbers of products and a price per product per user. My solution looks like this:
Table Customer:
CustomerID
...
Table Products:
ProductID
...
Table Prices:
CustomerID
ProductID
Price
Now my question: is it possible to get a Pricelist with one customer and all his prices in one row? E.g.:
CustomerID ; PriceProduct1 ; PriceProduct2 ; ....
So one Price-Column per Product, one Row per Customer? Can i do something like that with an sql-statement , view or stored-procedure so the number of Columns in the result depends on the number of Products and should be "dynamic" - which means when i add a new product to the product-table a new price-column is appended to the result?
I have to take a hierarchical XML file and store it into a table.
I'm looking at the XML Source and it shows me all of the various XML tags as separate tables. I know that a lot (if not all) of the data can be denormalized so that I have flattened records in one table instead of multiple tables.
I was hoping the XML source would allow me to designate how to denormalize the data but it doesn't seem like I can without using a bunch of sorts and merges in the data flow to denormalize it which I'm trying to avoid.
Does anyone know if SSIS has any easy way to flatten XML data? I know about XSL transforms but I was hoping that there might be an easier method.
I have a problem that I am hoping somebody can help me with!
I have built a hierarchy using the adjacency list model so I have records with an id that maps to the parent record so my hierarchy looks something like this:-
Newspapers
National Newspapers
Daily Express
Express Publications Express Supplements Daily Mail
and so on. What I would like to be able to do is flatten out the hierarchy so I get something like below where each level is in a column.
NewsPapers National Newspapers Daily Express Express Publications NewsPapers National Newspapers Daily Express Express Supplements
Ive used CTE's for displaying the hierarchy and producing aggregate figures when joing the hierarchy to spend information but am struggling to come up with any code that would produce a flattened hierarchy.
Hello everyone, this is my first post here so hopefully I am not asking a common question.
I am trying to create a flat dataset in SQL 2005. Basically I run a query and I get multiple rows for the same primary key. The query I am running is quite large and has several different tables connected to it, here is a small sample of what it looks like...
Typeid(Primary Key) Individual Address
1 Sam 912 Ave. J
1 Sam 913 Ave. Q
1 Sam 914 Ave. R
2 Mike 1000 Ave. O
3 Jill 1001 Ave. O
I want it to kind of look like this
TypeID Individual Address_1 Address_2 Address_3
1 Sam 912 Ave. J 913 Ave. Q 914 Ave. R
2 Mike 1000 Ave. O
3 Jill 1001 Ave. O
As I said before, this query is pretty big, and has several variables like Address where multiple rows are being taken by one Primary Key.
If it is not possible to do this in SQL 2005, is there a program that may be able to? Right now we are using SPSS as sort of a bandaid... we run the query in small portions like the one in the sample and then restructure the in sections but this takes several hours.
Anyways, thanks for any help that you may be able to provide.
Hello!I have a question about the SqlDataSource object.If i make an SqlDataSource with the following sql statement: "SELECT COUNT(id) AS recordCount FROM tblCategory"How do i get the recordCount value to a Variable.Im writing in C#.<asp:SqlDataSource ID="SqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT COUNT(id) AS recordCount FROM [tblCategory]"></asp:SqlDataSource>
The objective is to configure on a dev machine. Selecting New Database User is the easy part as the ***ASPNET user can be selected from the drop down listbox. What follows and what to do next is a myriad of choices. What needs to be done next? Is there a step-by-step document somewhere that you can refer?
I'm trying to do a fulltext search which returns the adjacent words also in the result, like u do a google search and it returns the paragraph containing the searched phrase.
Hi,Trying to get a grip on the "join" thing :)Up until now, I allways used this kinda method:"(select t1.a from t1 where t1.b in (select t2.ab from t2 where t2.b=0))"How can this be accomplished using joins? And if you have the time pleaseexplain the "bits" :)Thank you.--?TH
In a DataFlowTask with several OLE DB Destinations, how can I "last", before ending this DFT execute a storedProcedure?
This storedprocedure is used for saving metadata (taskname, rowcounts etc) regarding this DFT and I dont want to add an ExecuteSQLTask after the DFT in the Control Flow
I would like to gain data from a temporaly table created by an EXEC command. e.g. EXEC('SELECT col1, col2, col3 FROM Table WHERE ...') - that's right. But I would like to use it: SELECT * FROM _ThisTempTableTheExecCommandHasCreatedRigthNow WHERE...
I know that a function can return a table but an sp cannot.
This came from our actuaries, - create a formula generation and processing engine.
They have a dozen of well-normalized base tables that contain statistical data on competitors, broken down by state, county, city, and zip.
The result should contain aggregated breakdown by the same set of data elements, but also include along with other things an input formula and its result for the Base Rate.
The whole thing is a mini-Monte Carlo simmulation.
THE TRICK: If the formula today looks like:
x = ((a + b) * (c - d + e)) / (a * (c - d) * (f - d) * (e + d))
then tomorrow it may look like:
x = a * b * c * d * e * f
THE REAL TRICK: It should not be based on dynamic SQL!!!
I'm novice to SSIS and looking for some help on SSIS dtexec (SQL Server 2005).
Is it possible to change the BulkInsertTableName when running a package via dtexec /SET?
My test scenario contains:
- SQLServer 2005 SP2, servername: SDPM01, instancename: GWLINST1, databasename 1: TEST, databasename 2: DEV, tablename 1: Test_Table1 (both in TEST and DEV database), tablename 2: Test_Table2 (both in TEST and DEV database)
- 1 Data Flow task in BIDS (SSIS)
- 1 Data Flow Source: Flat File Source (Flat File Connection Manager name: FTP File Output + CSV file with a few lines of data that needs to be inserted in a SQL Server table)
- 1 Data Flow Destination: SQL Server Destination (OLE DB Connection Manager name: SDPM01GWLINST1.TEST
I can dynamically change the name of the database via:
Hi. I have data which comes as a string like"... Store #61"but sometimes it is"... Store 61"and sometimes it is"... Store 061"For three digits, it might be "... Store 561" or "... Store #561", or"... Store 0561".....The only thing I can be sure of is that the last 2 or 3 (significant)digits of this field represent the StoreNumber.I have to link this table on field StoreNumber with another table wherethe data is ALWAYS like 0061, 0561, etc, so always four digits, paddedwith zeroes.I'd like to use the equivalent of the VB functionFormat(StoreNumber), "0000"), but Format does NOT exist in TSQL.How could I solve this problem ? Please bear with me - I'm a beginnerin SQL...Thank you very muchAlex.
Hi.I have a "union" table which results of a union of two tables.Occasionally I could have duplicates, when the same PIN has been addedto both tables, albeit at different Datees/Times, such as:PINNameAdded Date100411A7/11/2007 10:12:58 AM100411A7/17/2007 10:54:23 AM100413B7/11/2007 10:13:28 AM100413B7/17/2007 10:54:39 AM104229C7/6/2007 2:34:13 PM104231D7/6/2007 2:34:25 PM104869E6/10/2007 11:59:12 AM104869E6/22/2007 2:40:18 PMThe question is - how can I delete by queries the first occurence(time-wise) of these duplicates - i.e. I would want to delete thefirst occurence of 100411 (A), the first occurence of 100413 (B), andthe first occurence of 104869 (E) in the example above - records C andD show only once, so they are fine.Is there a MsAccess solution ? Is there a SQL-server solution ?Thank you very much !Alex
I want to execute a dynamically generated sql-statementfrom inside an user-defined-function. Calling functions andextended stored-procs is allowed so I tried sp_executesqlas well as sp_prepare/sp_execute ....but both fail with an error 'only functions and extended stored-procsmay be called from inside a function.'any idea where I might be wrong ?thx in advance,Joerg--************************************************** ***********Joerg ClausmeyerMedizinische Informatik und DatenmanagementCHARITE - Universitätsmedizin Berlin************************************************** ***********
Could someone help me with the most efficient way to update a date field. Ihave a field with Date and Time in it but I want to update it with only theDate and strip off the time. Any ideas for the shortest way to do this?
We are thinking about buying new harddrives to improve sql server performance. Currently TEMPDB is running on a dedicated RAID 0 with 3 harddrives of 136 GB, 10.000 RPM. When running a large bulk insert within a SSIS package to 15 destination tables we notice high numbers in the Avg. and current Read Queue length (above 3000) of the drive where TEMPB is on. No other programs or swap file is using this RAID 0 drive. Can anyone tell me if it is worth buying 4 harddrives of 15.000 RPM each 33 GB big replacing the current 3 drives? How much impact will it have on the Avg. and Current Read queue length and will it improve the time sql server needs to bulk insert data?
(Didn't know in wich group this question belongs....) Hi group,
In QA there is an option called 'Script object as....' Were developing a lot of sp's. When I use this option the 'skeleton' of a sp is created. Eg Create proc uspMySP @Message varchar(10) AS SELECT @Message
The mention option would generate the following: DECLARE @RC int DECLARE @Message varchar(10) -- Set parameter values EXEC @RC = [EMGLSP3].[EM3_OWN].[uspMySP] @Message
The question: Is it possible to customize this? I would like to *** SET statements... Is this possible?
I am looking for a way to remove the printer symbol from the report manger because it dows not work on some client machines because they are locked down. For us it is completely sufficient to export to PDF.
Is there a setting or an entry in the web config or something else. A hint would be helpful
I only get an index telling me the index of the outputcolumns, but I also have a lot of inputcolumns. How do I get the correct index so I can take some data from an inputcolumn, modify the data and add it to the outputcolumn I specified earlier?
I use the ProcessInput to get the buffer, but as I dont know the correct index it's impossible for me to alter it to the output.
I have something in mind as altering the inputcolumns when overiding the ProcessInput like...
string str = buffer.GetString(columnIndex); //this is a predefined index I know
buffer.SetString(columnIndex, str.ToUpper()); // this should point to the outputcolumn, but I cant get the correct index for that purpose.
Can someone show me the command string required to write data out to 2 or 3 tables at once? How about how to write 2 entries at once? Write now my solution writes 1 record to a table and then I write somewhere around 40-200 records to a second child . Write now I'm writing like this:Open --> Add 1 record in table #1 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeand I'm just wondering if there is a better approach, such as writing all the data at once. Something like:Open --> Add 1 record in table #1 --> Add 5 records in table #2 --> close
