Merging in SQL

Other SQL Server Topics
1

Hi Everyone!
I am still fairly green at SQL so forgive me but I need help writing a Select statement. I currently have a few tables like this.

ID  | Date
2     4-16-18
4     4-16-17

Table 2
ID  | Name | Data
1     Blue    H
2     Blue    HM
3     Green   H
4     Green   HM
5     Red     H

I am joining at ID. You can see in Table 2 that some items have the same name. What I need to do is take the data from Table 1 say ID 2 and do what Excel would call a Merge based upon name so that the data for ID 2 can be added as another column for ID 1. I know excel has the capability to do this. I think SQL has a merge feature but (correct me if I'm wrong) I think that actually changes data. I am just trying to run a Select statement.

If anyone has any advice on what to do that would be appreciated.

Thanks!

Edit:
These are the result I am trying to produce with the Select statement. I now realize I forgot to mention the entries labeled HW will be filtered out once their data is merged.


ID  | Name | Data | Date
1     Blue    H    4-16-18
3     Green   H    4-16-17
5     Red     H    Null
2

Hello @wave2453,
When posting to a SQL forum it is proper etiquette to post your question with consumable data, i. e. create table statement(s) with inserts of sample data and what you've tried so far and desired result.

3

I am a little confused by what your asking.

I didn't give sample data but I gave data in a way that could still be used as an example.
As for what I have tried I haven't tried anything. I have zero idea of how to do this.
I will edit my original post to show what result I am looking for.

4

A create table statement
An insert statement
What I've tried is....
What I want is as a result is ....

5

It looks like you want:

SELECT B.ID, B.[Name],  B.[Data], A.[Date]
FROM TABLE2 B
LEFT JOIN TABLE1 A ON B.ID=A.ID
AND B.[Data]='H';
6

Hmm ok I get you. I tired to put it in here but new users can only put 1 image per post but my original post should be closer to what you were looking for now.

7 
CREATE TABLE dbo.Table_1 (
    ID int NOT NULL,
    Date date NULL
    )
INSERT INTO dbo.Table_1 VALUES(2,'20180416'),(4,'20170416')

CREATE TABLE dbo.Table_2 (
    ID int NOT NULL,
    Name varchar(30) NOT NULL,
    Data varchar(10) NOT NULL
    )
INSERT INTO dbo.Table_2 VALUES(1, 'Blue', 'H'),(2, 'Blue', 'HM'),(3, 'Green', 'H'),(4, 'Green', 'HM'),(5, 'Red', 'H')
1 Like
8

So if I am reading it right your output would look something like this

ID  | Name | Data | Date
1     Blue    H    Null
2     Blue    HW   4-16-18
3     Green   H    Null
4     Green   HW   4-16-17
5     Red     H    Null

When what I am needing is something like this

ID  | Name | Data | Date
1     Blue    H    4-16-18
2     Blue    HW   4-16-18
3     Green   H    4-16-17
4     Green   HW   4-16-17
5     Red     H    Null
9

My current though is to take Table 2 and Alias it and join the Alias with Table 1 by ID then join Aliased Table 2 to Table 2 by Name. Though I don't know if that would work or how to write that.

10

Here you go:

CREATE TABLE dbo.Table_1 (
    ID int NOT NULL,
    Date date NULL
    );
GO

INSERT INTO dbo.Table_1 
VALUES (2,'20180416')
   ,   (4,'20170416');

CREATE TABLE dbo.Table_2 
   (ID int NOT NULL
   ,Name varchar(30) NOT NULL
   ,Data varchar(10) NOT NULL
   );
GO

INSERT INTO dbo.Table_2 
VALUES (1, 'Blue', 'H')
   ,   (2, 'Blue', 'HM')
   ,   (3, 'Green', 'H')
   ,   (4, 'Green', 'HM')
   ,   (5, 'Red', 'H') ;

SELECT h.ID 
   , h.Data
   , t.Date
FROM
   (SELECT ID, [Name] FROM dbo.Table_2 WHERE [Data]='HM') hm
RIGHT JOIN
   (SELECT ID, [Name], [Data] FROM dbo.Table_2 WHERE [Data]='H') h ON h.Name = hm.Name
LEFT JOIN dbo.Table_1 t ON t.ID = hm.ID
GROUP BY h.ID, h.Name, h.Data, t.ID, t.Date;

DROP TABLE dbo.Table_1;
DROP TABLE dbo.Table_2;
GO

image

1 Like