I am still fairly new to SQL, having been tasked with creating a csv file from data now someone else has left.
I can do the csv export using sqlcmd and I have the query sorted and am pulling out the right data, but it generates two rows, as one of the tables has multiple records per cardholder. See the query below, I know there is a way of doing it with XML PATH, I think, but it has got me slightly confused.
have tried joining several tables and the result displays duplicate rows of virtually every line/row. i have tried using distinct but failed miserably.
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
I have a query that I'm working on, but instead of giving the query, I wanted to ask a basic syntax question. If more info is needed, let me know. If you have 2 rows that have a common relationship, but differing information in some fields, can you merge them all onto one row? I've done this with Sum(case) expressions, but I don't want to 'add' anything. In the following example, the ActivityID refers to a break. ActivityID can be:
0=Pick up 1=Drop Off 2=Lunch 3=Break
So if I wanted to see 2 breaks on 1 row in the following example, would this be possible:
I'm using a shipping program called endicia professional that allows for database manipulation to make my processing easier. I've managed to fix the database here and there but have had an issue combining orders from a single customer when theybuy more than one item. Ideally I would like to have it combine rows when a customer purchases items going to the same address. To avoid having an issue where the address line is the same ie two people live in the same appt complex and it combines these I thought we could use qualifiers as the purchase will have name, order Id that should be unique enough
Order-id name address sku 1234 John 46 easy ln. A27 1234 John 46 easy ln. B32
Results: Order-id name address sku 1234 John 46 easy ln. A27,b32
Hi Im having trouble with this it seems simple enough but its not!
I have a source Table called Access_table example
Name Role1 Role2 Role3 Role4 Role5 a 1 0 0 0 0 a 0 0 1 0 0 b 1 0 0 0 0 c 0 1 0 0 0 d 0 0 0 0 1 e 0 0 1 0 0 e 0 1 0 0 0 f 1 0 0 0 0 g 0 0 1 0 0
I need to create a view that basically finds all the names with double Roles and merge the results into 1 row example. Name Role1 Role2 Role3 Role4 Role5 a 1 0 1 0 0 e 0 1 1 0 0
I cannot change the information in the source table and the results need to be in a view as the roles will change. Every time I try and do this I duplicate the row again. Can anybody suggest a solution.
Hello All,I have an issue with dupliate Contact data. Here it is:I have a Contacts table;CREATE TABLE CONTACTS(SSN int,fname varchar(40),lname varchar(40),address varchar(40),city varchar(40),state varchar(2),zip int)Here is some sample data:SSN: 1112223333FNAME: FRANKLNAME: WHALEYADDRESS: NULLCITY: NULLSTATE NYZIP 10033SSN: 1112223333FNAME: NULLLNAME: WHALEYADDRESS: 100 MADISON AVECITY: NEW YORKSTATE NYZIP NULLHow do I merge the 2 rows to create one row as follows:via SQL or T-SQLSSN: 1112223333FNAME: FRANKLNAME: WHALEYADDRESS: 100 MADISON AVECITY: NEW YORKSTATE NYZIP 10033Pointers appreciated.Thanks
I need to populate a table from several sources of raw data. For agiven security (stock) it is possible to only receive PARTS ofinformation from each of the different sources. It is also possibleto have conflicting data.I am looking to make a composite picture of a given security using thefollowing rules:1) The goal is to replace all NULL and Blank values with data2) Order of precedence (from highest to lowest) is Non-NULL Non-Blank--> Blank --> NULL3) In the case of Non-NULL Non-Blank values that conflict (aredifferent) leave existing value (even if NULL or Blank)For example:Given the following rows:Symbol Identity IdSource Exchange Type SubType Name-------- ------------ --------- --------- ------- ---------------------------TZA 901145102 CUSIP XNYS Stock NULL TV AZTECATZA 901145102 NULL NULL NULL NULLWSM 969904101 CUSIP XNYS Stock NULL WILLIAMSSONOMAWSM 969904101 NULL XNYS Stock NULLWILLIAMS-SONOMAWSM CUSIP XNYS Stock Common NULLWSM NULL CUSIP XASE Stock NULL WILLIAMSSONOMATYC 902124106 CUSIP XNYS Stock NULL TYCOTYC 902124106 CUSIP XNYS Stock NULL TYCOINTERNATIONALI am looking for the following results ('*' indicates changed value)Symbol Identity IdSource Exchange Type SubType Name-------- ------------ --------- --------- ------- ---------------------------TZA 901145102 CUSIP XNYS Stock NULL TV AZTECATZA 901145102 *CUSIP *XNYS *Stock NULL *TV AZTECAWSM 969904101 CUSIP XNYS Stock *Common WILLIAMSSONOMAWSM 969904101 *CUSIP XNYS Stock *CommonWILLIAMS-SONOMAWSM *969904101 CUSIP NULL Stock Common NULLWSM *969904101 CUSIP XASE Stock *Common WILLIAMSSONOMATYC 902124106 CUSIP XNYS Stock NULL TYCOTYC 902124106 CUSIP XNYS Stock NULL TYCOINTERNATIONAL
I have 2 columns (ID, Msg_text) in a table where i need to combine every 3 rows into single row. What would be the best option i have? I know by using 'STUFF' and 'XML PATH' i can convert all the rows into a single row but here i'm looking for every 3 rows into a single row.
I have this query and it works except for I am getting duplicate primary keys with unique column value. I want to combine them so that I have one primary key, but keep all the columns. Example:
Key column 1 column 2 column 3 column 4 A 1 1 A 2 2 B 2 3 B 5 5
it should look like:
A 1 1 2 2 B 2 3 5 5
Here is my query:
SELECT * FROM [TLC Inventory].dbo.['2014 new$'] WHERE [TLC Inventory].dbo.['2014 new$'].mis_key LIKE '2%' AND dbo_Product_Info#description NOT LIKE 'NR%' AND dbo_Line_Info#description NOT LIKE 'OBSOLETE%'
Please point me in the direction of a tutorial that will do help me do the following:
The database has been previously created and the users first & last names are in the respective columns. There is also a column that should contain the full name of the user but as such it only contains <NULL>. Is there a way to pull the first name and last name from the db then write both names to column3?
I have a source table in the staging database stg.fact and it needs to be merged into the warehouse table whs.Fact.
stg.fact is not a delta feed it is basically an intra-day refresh.
Both tables have a last updated date so its easy to see which have changed.
It will be new (insert) or changed (update) data that I am interested in, there are no deletions.
As this could be in the millions of rows that are inserts or updates then this needs to be efficient.
I expect whs.Fact to go to >150 million rows.
When I have done this before I started with T-SQL Merge statement and that was not performant once I got to this size.
My original option was to do this is SSIS with a lookup task that marks the inserts and updates and deal with them seperately. However I set up the lookup tranformation the reference data set will have a package variable in the SQL commnd. This does not seem possible with the lookup in 2012! Currently looking at Merge Join transformation and any clever basic T-SQL that could work as this will need to be fast, and thats where I think that T-SQL may be the better route.
Both tables will have >100,000,000 rows Both tables have the last updated date The Tables are in different databases but on the same SQL Instance Each table holds 5 integer columns, one Varchar, one datatime
Last time I used Merge it was a wider table with lots of columns so don't know if this would be an option.
We are trying to consolidate sales order data from different sales locations where in which we have to merge multiple [more than 25] MSDE 2000 databases into a Single DB. What is the best way to do this?
At the end of the day i should have one DB which contains sales order data of all the sales locations.
Greetings... I'm working at consolidating several Reporting Services installations into one so we can decommission some old servers. We have two Report Servers that are in SQL Server 2000 and three that are in SQL Server 2005 (none are in a web farm configuration). I can upgrade the 2000 databases to 2005 format just fine, but now how do I merge these five ReportServer/ReportServerTempDB databases into one? I'm reluctant to redeploy reports to the "official" Report Server because we lose security, subscription, and scheduling information by doing that. So redeploy would cost us time and data loss over hundreds of reports, thus my hope to merge the databases at a data level. Is there a tool to automate this process, or has anyone tried this before? Thanks in advance.
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :- SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
CREATE TABLE [dbo].[Competition] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (150) NOT NULL, [Remarks] NVARCHAR (350) NULL, [Start] DATETIME NOT NULL, [IsActive] BIT NULL, [End] DATETIME NOT NULL,
[Code] ....
Then here is my query with the problem below. The problem is that multiple EntryImages can relate to a single Entry. My goal is to only select one Row for each Entry (using @EntrySelection) but when I join to EntryImages I always get back multiple rows when an Entry has multiple EntryImages.
Declare@EntrySelection int= 10 select top (@EntrySelection) [dbo].Entries.Id, [dbo].Entries.CatchDate, [dbo].Entries.CompetitionId, [dbo].Competition.Name,
Is there a way to create a query that will return only 1 row within a join for example:
select a,b,c,d from tbl1 inner join tbl2 on top 1 tbl2.a = tbl1.a <-- return only the top record here...
I dont have too much of a problem with joins... however I was wondering if there was a mechanism to just specify what would be the top most record from a join clause.
have the following code for ONE Inner Join, but I want to add another join for another Table and Fields.... can you help me with the syntax: SELECT DISTINCT
I cant find the problem with this query for the life in me SELECT ForumTopic.*, websiteinfo.shortdomainname AS author, MemberInfo.postcount AS pc, MemberInfo.joined AS jd FROM ForumTopic INNER JOIN websiteinfo ON ForumTopic.domaininfoid=websiteinfo.domaininfoid INNER JOIN websiteinfo websiteinfo2 ON MemberInfo.domaininfoid=websiteinfo2.domaininfoid WHERE ForumTopic.id = 1 the error message is: Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "MemberInfo.domaininfoid" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "MemberInfo.postcount" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "MemberInfo.joined" could not be bound.
Can anyone help with this error? Thanks for any responses
I need to create a view which links 5 tables as follows: I have a Header Table which is keyed on Product and Year which I want to join to a Detail Table which is keyed on Product and Year and Week. I want to see all of the rows from each table, which I think is a FULL OUTER JOIN.
I then have three subsidiary tables for Sales, Orders and Deliveries which are all keyed on Product and Year and Week - I want to join each of these tables separately to the Detail table above so that again I see all of the rows from the Detail Table, the Sales Table, the Orders Table and the Deliveries table. For any Product/YearWeek there may or may not be a row on any of the Sales, Order or Deliveries table, but there will not be any rows on these tables which are not on the Detail Table.
Can I do this in the FROM clause andnif so how, or do I need to do a series of separate SELECTs for the Sales, Orders & Deliveries table with UNION clauses. Best regards Colin
Application called Filemaker, I'm now trying to use that experience to teach myself SQL (using Microsoft SQL is SQL 2008 r2).My question is can you have multiple self joins.
I have a table which has two columns (for this example)
spell | spellindicator | episode
the spell is not unique e.g. there could be several records with the spell 'A1234' the spellindicator column will contain a number 1 or 2. the data could look like this:
Part 1) works fine on its own Part 2) works fine on its own
But put both joins in the same statement and the count function no longer works correctly. I realise that there is an issue with the self joins but I thought it would be possible to have multiple self joins.
use leis go select l1.spell, l1.spellindicator, l1.episode, l1.pk_Indicator, l1.fk_indicator ,maxepisode = MAX(e2.episode) ,minepisode = MIN(e2.episode)
I have a customer table which has customer billing addresses; an orders table which has payment and shipping method info; and an items table which has line items for each order. The customer table has a custnum index field. The orders table has a custnum and orderid field. And the items table has an orderid field.
I'd like to be able to retrieve, in one query, the customer's billing address, all of their orders, with each of their order's line items.
What would the query look like in order to accomplish this?
Here is what I am currently doing: ******************************************** SELECT Hours.Hours, Hours.Comments FROM Hours INNER JOIN Employee ON Employee.UserID = Hours.UserID INNER JOIN Task ON Hours.TaskID = Task.TaskID INNER JOIN Project ON Hours.ProjID = Project.ProjID WHERE Hours.Date <= EndDate AND Hours.Date >= StartDate AND Hours.Date <= EndDate; ******************************************** Am I doing something wrong here? Any help would be greatly appreciated!
I'm trying, with little success, to achieve something that should be quite easy (I think!) and any advice would be appreciated.
I have a leagues table structured so:
LeagueID | Name | Player1 | Player 2 ... Player6
and the data in the player columns is a userid from the users table and I'm trying to display the Leagues but with the player names rather than player IDs.
I'm working along the lines of
Code:
select u1.displayname as Player1, u2.displayname as Player2 from DCMLeagues as L inner join Users as u1 on L.player1 = u1.userid inner join Users as u2 on L.player2 = u2.userid
but with little success so far. Any thoughts would be appreciated! Thanks very much in advance.
I'm putting together the site for a local darts league, essentially each of the teams completes an 'e-scorecard' for that particular game. This is then stored in a table named 'scorecardIndex' :
So each 'index' points off to the UID of the player who was captain, and the UID of of each team.
What I'm struggling with is joining this all together, as I'm joining both scxTeam1Capt and scxTeam2Capt to the same columns in the 'players' table (plFirstName, plSurname)
Could somebody tell me what is the secret of being able to write a SELECT statement having mulitple LEFT or RIGHT joins, I seem to get in trouble as soon as I add the second LEFT join, as I am obviously doing it wrong.
These are my tables, would somebody mind having a go,or explaining what do i need to be aware of in a case like this
