Merging Multiple Rows From Joins
Dec 15, 2014
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.
set nocount on
selectdbo.card.EncodedNumber,
dbo.card.IsEnabled,
dbo.Cardholder.FirstName,
dbo.cardholder.LastName,
dbo.card.ExpiryTime,
dbo.PersonalDataString.Value
[Code] .....
View 2 Replies
ADVERTISEMENT
Jul 10, 2014
have tried joining several tables and the result displays duplicate rows of virtually every line/row. i have tried using distinct but failed miserably.
select purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,
[code]....
View 4 Replies
View Related
Nov 15, 2006
Hi!
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...)
Any pointer most welcome!
best regards and thanks
Thibaut
View 1 Replies
View Related
Jun 28, 2006
Say I have a table with the columns (and example data):
CustomerNo, ContactNo, ActivityNo
null, null, 1
100, null, 1
null, 666, 1
null, null, 2
200, null, 2
null, 777, 2
From this I would like to get the result:
CustomerNo, ContactNo, ActivityNo
100, 666, 1
200, 777, 2
How do I solve this. Im getting grey hair here...
View 5 Replies
View Related
Jan 20, 2008
Hi All,
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:
Veh ActID ArrTime DepTime
1 3 7:00 8:00
1 3 10:00 11:00
Veh ActID ArrTime DepTime ArrTime DepTime
1 3 7:00 8:00 10:00 11:00
Thanks in advance for your help!
Craig
View 4 Replies
View Related
Oct 20, 2006
Hello everyone!
Maybe you can help me out:
I have 2 tables (A and B), Table A has 2 fields t1 and t2...each of them has a number that is related to the primary key of Table B.
What i want is to make a query that presents:
t1,t2, B.descriptionof_t1, B.description_t2
Anyone that can help me?
Thanks in advance...
View 5 Replies
View Related
Mar 6, 2008
Hi all,
I'm facing the following problem:
TextData ObjectID SPID StartTime EndTime
------------------------------------------------------------------------------------------------------------
Select 1 111111111 52 2008-03-06 11:19:51.250 NULL
Select 1 111111111 52 NULL 2008-03-06 11:19:51.250
I want to achive this result by either an update statement or a select query:
TextData ObjectID SPID StartTime EndTime
------------------------------------------------------------------------------------------------------------
Select 1 111111111 52 2008-03-06 11:19:51.250 2008-03-06 11:19:51.250
Is this possible? There is no primary key
Thanks!
Rgds,
Worf
View 5 Replies
View Related
Jul 15, 2007
it doesn't appear possible to merge cells by col in RS 2005 ? am i missing some more advanced feature or is it a great big over sight on MS's part?
i basicly want a 3 column table, with the rows in the first column merged and the text turned on it's side showing bottom to top.
pretty ordinary kind of thing to do i would have thought
View 9 Replies
View Related
Mar 5, 2012
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
View 6 Replies
View Related
Apr 29, 2004
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.
Thanks in advance.
View 2 Replies
View Related
Jul 23, 2005
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
View 5 Replies
View Related
Jul 20, 2005
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
View 6 Replies
View Related
Aug 18, 2015
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.
View 3 Replies
View Related
Jan 14, 2015
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%'
Do I use a sum function?
View 7 Replies
View Related
Jul 8, 2015
I have a table data as shown below.
IDFNLN
1x
1y
2a
2b
3g
4t
I want output as shown below.
IDFNLN
1xy
2ab
3g
4t
I want the two duplicate rows to be merged into one. How to achieve it.
View 10 Replies
View Related
Mar 20, 2001
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?
Thanks - Sharon
View 5 Replies
View Related
Aug 18, 2014
SQL 2012
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.
View 6 Replies
View Related
Jun 3, 2008
Hi,
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.
Thanks,
SakthiVenkatesh.
View 5 Replies
View Related
Sep 18, 2007
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.
Austin
View 8 Replies
View Related
Sep 1, 2005
Hi All,
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.
View 2 Replies
View Related
Apr 2, 2015
Let me start off with my table layouts.
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,
[Code] ....
View 2 Replies
View Related
Mar 7, 2008
Using MS SQLServer 2000
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.
thanks
Kevin
View 1 Replies
View Related
Aug 23, 2007
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
View 1 Replies
View Related
Feb 19, 2008
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
View 3 Replies
View Related
Feb 3, 2006
Hello,
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
View 1 Replies
View Related
Mar 28, 2012
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:
spell-------spellindicator---------episode
A1234-----------1-----------------2
B5674-----------1-----------------1
C8739-----------2-----------------3
A1234-----------1-----------------1
B5674-----------2-----------------1
A123------------2-----------------2
A1234-----------1-----------------3
I want to create two left self joins:
1) to count the records that have the same order number with the spellindicator being 1.
2) to find the max and min episode
For 1) I've created two keys:
pk_indicator = spell +':1'
fk_indicator = spell + ':' + spellindicator
example data:
pk_indicator------fk_indicator
A1234:1-----------A1234:1
B5674:1-----------B5674:1
C8739:1-----------C8739:2
A1234:1-----------A1234:1
B5674:1----------- B5674:2
A1234:1-----------A1234:2
A1234:1-----------A1234:1
For2) The join is on the spell
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)
[code]....
View 2 Replies
View Related
Oct 23, 2007
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?
Thanks in advance.
View 1 Replies
View Related
May 7, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
ID Pat_ID
1 A
2 A
3 A
4 A
5 A
6 B
7 B
8 B
9 C
10 D
11 D
I want output for the above as:
Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null
Please help me. Thanks!
View 6 Replies
View Related
Mar 7, 2001
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!
View 2 Replies
View Related
Oct 29, 2006
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.
-- Chris
View 5 Replies
View Related
Apr 9, 2012
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' :
scxUID(PK)scxTeam1(int)scxTeam2(int)scxTeam1Capt(int)scxTeam2Capt(int)scxDate(nvarchar 50)scxSubmitBy(int)
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)
plUID(PK)plFirstName(nvarchar 50)plSurname(nvarchar 50) plTeamUID(int)plEmail(nvarchar80)plPassword(nvarcharMAX)
And equally joining scxTeam1 & scxTeam2 to the Teams table to pull back the teams actual name.
Using several inner joins I can pull back the entire row for each team, but the columns have the same name..
SELECT *
FROM scorecardIndex AS S
INNER JOIN teams AS T1
ON T1.teamUID = S.scxTeam1
INNER JOIN teams AS T2
ON T2.teamUID = S.scxTeam2
I'm using MS SQL Server 2008 R2.
I've tried to be as detailed as possible without overdoing it.
View 3 Replies
View Related
Apr 27, 2004
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
emailDetails table ( emailID_PK,emailName,emailText,emailSubject,emailN otificationTypeID)
emailRecipients table (emailID_PK, RecipientID, SentToEmail)
luEmailNotificationTypes look up table (emailNOtificationTypeID_PK, emailNotificationTypeName)
Thanks
View 2 Replies
View Related
Dec 7, 2013
Table 1:
id amount
1 100
2 200
3 300
4 400
Table 2:
id amount
1 100
1 100
2 200
3 300
4 null
Table 3:
id amount
1 null
2 200
2 200
3 300
3 200
4 null
id is common for each tables , how can i get output like this:
Collapse | Copy Code
id t1 t2 t3
1 100 200 null
2 200 200 200
3 300 300 500
4 400 null null
I am stuck with this query .
View 1 Replies
View Related
