Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

SQL Server - Query help

Hello Experts,

 I have this SQL Query:

Select A.AddressName, A.NameFirst,A.NameLast, A.Address1, A.Address2,
A.City, A.StPrId, A.PostalCode from edgePier1.dbo.OrderHeader OH
JOIN edgePier1.dbo.Addresses A
ON OH.MemberId = A.MemberId
Where OH.OrderHeaderId = 54000329

Results:

AddressName NameFirst NameLast Address1 Address2 City      StPrId PostalCode


Shipping address 1      John      Seabaugh      123 E Main St      NULL      Grandview      60      76050-1903

Billing address      John      Seabaugh      123 E Main St      NULL      Grandview      60      76050-1903


 i want to write a SQL that will show Billing and shipping address on the same line(single line.)

any idea?

Thanks
0
mani_sai
Asked:
mani_sai
  • 3
  • 2
  • 2
1 Solution
 
EvilPostItCommented:
Select REPLACE(REPLACE(A.AddressName,CHAR(13),''),CHAR(10),''), A.NameFirst,A.NameLast, A.Address1, A.Address2, 
A.City, A.StPrId, A.PostalCode from edgePier1.dbo.OrderHeader OH 
JOIN edgePier1.dbo.Addresses A 
ON OH.MemberId = A.MemberId
Where OH.OrderHeaderId = 54000329

Open in new window

0
 
mani_saiAuthor Commented:
your query is showing results on 2 lines...

I want shipping and billing information on the same line....
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Is there a column in dbo.addresses that uniquely identifies the customer?

If so, then it would be a subquery that goes something like this <air code, replace the obvious>...

SELECT
 sa.NameFirst as ship_NameFirst,
 sa.NameLast as ship_NameLast,
 sa.Address1 as ship_Address1,
 sa.Address2 as ship_Address2,
 sa.City as ship_City,
 sa.StPrId as ship_StPrId,
 sa.PostalCode as ship_PostalCode,
 ba.NameFirst as bill_NameFirst,
 ba.NameLast as bill_NameLast,
 ba.Address1 as bill_Address1,
 ba.Address2 as bill_Address2,
 ba.City as bill_City,
 ba.StPrId as bill_StPrId,
 ba.PostalCode as bill_PostalCode
FROM edgePier1.dbo.OrderHeader OH
    JOIN ( SELECT AddressName, etc FROM Address WHERE AddressName = 'Shipping Address') sa ON oh.ThatColumn = sa.ThatColumn
    JOIN ( SELECT AddressName, etc FROM Address WHERE AddressName = 'Billing Address') ba ON oh.ThatColumn = ba.ThatColumn
Where OH.OrderHeaderId = 54000329
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
mani_saiAuthor Commented:
Great. It worked. Thanks.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
 
EvilPostItCommented:
Sorry, my mistake. Thought you were trying to remove a carriage return, line feed.
0
 
mani_saiAuthor Commented:
EvilPostIt - no problem.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now