Combining 2 field values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Combining 2 field values

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-18 : 11:03:58

Morning,

I know there is a way to do this but either I have not had enough coffee or I'm losing what's left of my mind.

I have 2 views:

view A has the syntax
select bodytype, facility where blah blah
results: drybox Aberdeen, reefer Aberdeen, special Aberdeen

view B has the same syntax
select bodytype, facility where blah blah
BUT the results here are: All Trailers Aberdeen

I want to combine the two so I get

reefer Aberdeen
drybox Aberdeen
special Aberdeen
All Trailers Aberdeen

I'm going to slap myself when I read the answer. I know it's that simple but the simple things are sometimes the least obvious :)

Thanks for any help you can offer.

Laura

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-18 : 11:23:28

select bodytype, facility FROM ViewA where blah blah
UNION ALL
select bodytype, facility FROM ViewB where blah blah

I think. If you follow the hint link below, and post like I suggest, we can be sure it's what you want.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-18 : 11:34:49

Thanks Brett,

UNION is almost it. I didn't post the code becasue its ssooooo long, lots of fields but I'll scale it down and just give the gory details. What I want, needy creature that I am, is b.BodyType and a.bodytype combined into one field.

Thanks as always

Laura


SELECT   b.Facility, 
b.BodyType, 
a.bodytype, 
b.UpLoadDate                    
FROM   dbo.vwYC8DayAll_1A as b INNER JOIN
dbo.vwAllTrailers as a ON b.Facility = a.Facility AND 
b.UpLoadDate = a.UpLoadDate

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-18 : 11:37:22

b.BodyType + a.bodytype AS NewBodyType
??

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-18 : 12:33:15

Well that gives me:
DryBoxAll Trailers
ReeferAll Trailers
SpecialtyAll Trailers

what I NEED is this:

reefer Aberdeen
drybox Aberdeen
special Aberdeen
All Trailers Aberdeen

Is it even possible? I'm starting to wonder....

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-18 : 12:39:50

Laura,

Please post a sample like I suggest in my hint link...I don't see it.

Sorry

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-18 : 12:57:36

Okay using this query


SELECT   b.Facility, 
b.BodyType, 
a.bodytype, 
b.UpLoadDate                    
FROM   dbo.vwYC8DayAll_1A as b INNER JOIN
dbo.vwAllTrailers as a ON b.Facility = a.Facility AND 
b.UpLoadDate = a.UpLoadDate

Gives me this result set:

Facility BodyType bodytype UpLoadDate
-------- ------------ ------------------------------
Aberdeen DryBox All Trailers 08/11/2005
Aberdeen Reefer All Trailers 08/11/2005
Aberdeen Specialty All Trailers 08/11/2005
Aberdeen DryBox All Trailers 08/12/2005
Aberdeen Reefer All Trailers 08/12/2005
Aberdeen Specialty All Trailers 08/12/2005
Aberdeen DryBox All Trailers 08/13/2005
Aberdeen Reefer All Trailers 08/13/2005
Aberdeen Specialty All Trailers 08/13/2005

Using the code below:


SELECT   b.Facility, 
 b.BodyType + a.bodytype AS NewBodyType,
b.UpLoadDate                    
FROM   dbo.vwYC8DayAll_1A as b INNER JOIN
dbo.vwAllTrailers as a ON b.Facility = a.Facility AND 
b.UpLoadDate = a.UpLoadDate

I get:
Facility NewBodyType UpLoadDate
-------- ------------ ----------------------
Aberdeen DryBoxAll Trailers 08/11/2005
Aberdeen ReeferAll Trailers 08/11/2005
Aberdeen SpecialtyAll Trailers 08/11/2005
Aberdeen DryBoxAll Trailers 08/12/2005
Aberdeen ReeferAll Trailers 08/12/2005
Aberdeen SpecialtyAll Trailers 08/12/2005
Aberdeen DryBoxAll Trailers 08/13/2005
Aberdeen ReeferAll Trailers 08/13/2005
Aberdeen SpecialtyAll Trailers 08/13/2005

What I want is:

Facility BodyType UpLoadDate
-------- ------------ ------------------------------
Aberdeen DryBox 08/11/2005
Aberdeen Reefer 08/11/2005
Aberdeen Specialty 08/11/2005
Aberdeen AllTrailers 08/11/2005
Aberdeen DryBox 08/12/2005
Aberdeen Reefer 08/12/2005
Aberdeen Specialty 08/12/2005
Aberdeen AllTrailers 08/12/2005
Aberdeen DryBox 08/13/2005
Aberdeen Reefer 08/13/2005
Aberdeen Specialty 08/13/2005
Aberdeen AllTrailers 08/13/2005

Does that make it more clear? Thanks for the help

Laura

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-18 : 13:23:47

Laura, I was fishing for a format I could just cut and paste so I could figure it out, AND confirm that we are on the same page..

This gives your results as you want...but it doesn't make much sense


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE vwYC8DayAll_1A_99 (Facility  varchar(25), BodyType varchar(25), UpLoadDate datetime)
CREATE TABLE vwAllTrailers_99 (BodyType varchar(25), UpLoadDate datetime)
GO

INSERT INTO vwYC8DayAll_1A_99 (Facility, BodyType, UpLoadDate)
SELECT 'Aberdeen', 'Drybox All'   , '8/11/2005' UNION ALL
SELECT 'Aberdeen', 'Reefer All'   , '8/11/2005' UNION ALL
SELECT 'Aberdeen', 'Specialty All', '8/11/2005' UNION ALL
SELECT 'Aberdeen', 'Drybox All'   , '8/12/2005' UNION ALL
SELECT 'Aberdeen', 'Reefer All'   , '8/12/2005' UNION ALL
SELECT 'Aberdeen', 'Specialty All', '8/12/2005' UNION ALL
SELECT 'Aberdeen', 'Drybox All'   , '8/13/2005' UNION ALL
SELECT 'Aberdeen', 'Reefer All'   , '8/13/2005' UNION ALL
SELECT 'Aberdeen', 'Specialty All', '8/13/2005'

INSERT INTO vwAllTrailers_99 (BodyType, UpLoadDate)
SELECT 'Trailers'   , '8/11/2005' UNION ALL
SELECT 'Trailers'   , '8/11/2005' UNION ALL
SELECT 'Trailers'   , '8/11/2005' UNION ALL
SELECT 'Trailers'   , '8/12/2005' UNION ALL
SELECT 'Trailers'   , '8/12/2005' UNION ALL
SELECT 'Trailers'   , '8/12/2005' UNION ALL
SELECT 'Trailers'   , '8/13/2005' UNION ALL
SELECT 'Trailers'   , '8/13/2005' UNION ALL
SELECT 'Trailers'   , '8/13/2005'
GO


    SELECT Facility, BodyType, UpLoadDate
      FROM vwYC8DayAll_1A_99
 UNION ALL
    SELECT DISTINCT a.Facility, b.BodyType, b.UpLoadDate
      FROM vwYC8DayAll_1A_99 a
INNER JOIN vwAllTrailers_99  b
	ON a.UpLoadDate = b.UpLoadDate
  ORDER BY UpLoadDate, BodyType
GO

SET NOCOUNT OFF
DROP TABLE vwYC8DayAll_1A_99, vwAllTrailers_99

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-18 : 13:24:45

EDIT: In the future use this method to pose your problems.

Thanks

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-18 : 13:39:02

Thanks Brett. I never know if I'm posting to much. Sometimes people don't read if it seems like there is to much text. You just can't win :)

Thanks again

Laura

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-18 : 13:45:53

Typically a short paragraph explaining what your problem is then the DDL, DML for sample data, and the expected result set is all that we need to get started at least. I agree that long posts might not even be read, but keep in mind that the DDL, DML, and expected result set are needed for us to help you so don't consider this part when thinking about how long the post is.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-19 : 09:58:40

Yes but did I get it right?

You know you can just cut and paste the code and try it....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources