19721

SQL Combine multiple rows into one with multiple columns

I've got multiple rows in my result from my query:

for example, the table "Address":

Street | Number | City ---------------------- A1 | A2 | A3 B1 | B2 | B3

What I actually want is:

Address1_Street | Address1_Number | Address1_City | Address2_Street | Address2_Number | Address2_City ------------------------------------------------------------------------------------------------------ A1 | A2 | A3 | B1 | B2 | B3

Anyone who knows how I can achieve this?

I've managed to get to this point now (sorry for using other columns, the one above was an example, but I'll guess you'll get the point):

select distinct a.ID, a.Name, ca1.NameLine1 as Address1_NameLine1, ca2.NameLine1 as Address2_NameLine1 from dbo.Accounts a, dbo.Addresses ca1, dbo.Addresses ca2 where (a.ID = ca1.AccountID AND a.ID = ca2.AccountID) AND (a.Name = 'TEST') AND (ca1.ID <> ca2.ID)

But I'm still getting 2 rows... where Address1 switches with Address2. Anyone who knows how to only get one? Thanks!

Answer1:

Try:

select ID, max(Name) Name, max(case when rn=1 then NameLine1 end) Address1_NameLine1, max(case when rn=2 then NameLine1 end) Address1_NameLine2 from (select a.ID, a.Name, ca.NameLine1, rank() over (partition by a.ID order by ca.ID) rn from dbo.Accounts a join dbo.Addresses ca on a.ID = ca.AccountID where a.Name = 'TEST') sq group by ID

Answer2:

select address1.* ,address2.* from Address address1 inner join on Address address2 on address1.userid=address2.userid

you can choose inner or left join based on data.

Recommend

  • Compare Dictionary
  • Omit template arguments when create an instance of template class from another instance of template
  • Retrieve List of Cells in Excel Where Column Equals Something
  • Fortran: Array of arbitrary dimension?
  • How to run a query between dates and times?
  • Tips for creating scalable WPF user control
  • How to set `secure` and `httpOnly` for Plones `__ac` cookie?
  • Get current user from inside the model in Sails
  • Wrapping a c#/WPF GUI around c++/cli around native c++
  • EntLib Way to Bind “Null” Value to Parameter
  • Django model inheritance, filtering models
  • wxPython: displaying multiple widgets in same frame
  • What does 'Language neutral' mean with regard to MAKELANGID?
  • how to save the state in userdefaults of accessory checkmark-iphone
  • Chart.js Multiple dataset
  • Groovy: Unexpected token “:”
  • Android - Material Design - NavigationView - How to put vertical scroll?
  • x64 applications using gdi+: what are the consequences on performance?
  • How to write order and limit within cakephp joins array
  • Custom validator control occupying space even though display set to dynamic
  • Word Open XML Mail Merge
  • Android fill_parent issue
  • FileReader+canvas image loading problem
  • Pass value from viewmodel to script in zk
  • Using $this when not in object context
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Deselecting radio buttons while keeping the View Model in synch
  • Getting last autonumber in access
  • Counter field in MS Access, how to generate?
  • Is possible to count alias result on mysql
  • Incrementing object id automatically JS constructor (static method and variable)
  • How to check if every primary key value is being referenced as foreign key in another table
  • Knitr HTML Loop - Some HTML output, some R output
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • jquery mobile loadPage not working
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Benchmarking RAM performance - UWP and C#
  • How to stop GridView from loading again when I press back button?