HOw to combine 2 rows into a single Row

If you dont have a primary key on the table add one using an idenetity
column, then you can (untested)

select * from myNames Husband,
myNames Wife
where Wife.Id = Husband.Id+1
and Husband.id % 2 = 1

Obviously this requires pretty exact input data...

Dave


Pinky_n_need_of_a_brian wrote:
> I am trying to combine two rows out of a table into one single row with
> two fields.
>
> Here is my example.
>
> Select * from myNames
> {results}
> Name: Joe Done (row 1)
> Name: Jane DoMe (row 2)
>
> Name: John Snake (row1)
> Name: Katy Pipes (row2)
>
>
> {desired results}
>
> Husband: Joe Done Wife: Jane DoME (row 1)
> Husband: John Snake Wife: Katy Pipes (row 2)
>
>
> One the values are in the desired format I would like to select them
> using the new field name
>
> Example:
>
> Select Husband, wife from Sometable
>
>
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

To add to Dave's response, what do the rows that 'belong together' have in
common?


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Hi
create table #myNames (id int not null, name1 varchar(50),name2 varchar(50))

insert into #myNames values (1,'Joe Done','John Snake')

insert into #myNames values (2,'Jane DoMe','Katy Pipes')

Select * from #myNames

with cte

as

(

select n1.id as id_h,n2.id as id_w,

n1.name1 as n1 ,n2.name1 as n2,

n1.name2 as n3 ,n2.name2 as n4

from #myNames n1 join #myNames n2

on n1.id=n2.id+1

) select n2,n1 from cte

union all

select n4,n3 from cte





"Pinky_n_need_of_a_brian" wrote in message
news:edgC7S21IHA.6096@TK2MSFTNGP06.phx.gbl...
>I am trying to combine two rows out of a table into one single row with
> two fields.
>
> Here is my example.
>
> Select * from myNames
> {results}
> Name: Joe Done (row 1)
> Name: Jane DoMe (row 2)
>
> Name: John Snake (row1)
> Name: Katy Pipes (row2)
>
>
> {desired results}
>
> Husband: Joe Done Wife: Jane DoME (row 1)
> Husband: John Snake Wife: Katy Pipes (row 2)
>
>
> One the values are in the desired format I would like to select them
> using the new field name
>
> Example:
>
> Select Husband, wife from Sometable
>
>
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

As Matija put it, you may be having some data that can group the 2 related
rows.
Lets say you have data like

GroupId textValue columnName
1 Joe Husband
1 Jane Wife
2 John Husband
2 Katy Wife

you can use a join or may be a pivot.

Query with join
---------------------
SELECT h.textValue 'Husband'
,w.textValue 'Wife'
FROM myNames h
INNER JOIN myNames w
ON h.groupId=W.groupid
AND h.columnname='Husband' AND w.columnName = 'Wife'

Query with Pivot
-------------------------
SELECT groupId,
[Husband], [Wife]
FROM
(SELECT groupId, textValue,columnName
FROM myNames) AS SourceTable
PIVOT
(
MAX(textValue)
FOR columnName IN ([Husband], [Wife])
) AS PivotTable

Though I'm not sure how and why, what I observed was that the pivot ran
significantly faster on in memory tables while the join was good for the
physical tables. I have tried it on a table that had a million row and an
index on the groupId.

"Pinky_n_need_of_a_brian" wrote in message
news:edgC7S21IHA.6096@TK2MSFTNGP06.phx.gbl...
> I am trying to combine two rows out of a table into one single row with
> two fields.
>
> Here is my example.
>
> Select * from myNames
> {results}
> Name: Joe Done (row 1)
> Name: Jane DoMe (row 2)
>
> Name: John Snake (row1)
> Name: Katy Pipes (row2)
>
>
> {desired results}
>
> Husband: Joe Done Wife: Jane DoME (row 1)
> Husband: John Snake Wife: Katy Pipes (row 2)
>
>
> One the values are in the desired format I would like to select them
> using the new field name
>
> Example:
>
> Select Husband, wife from Sometable
>
>
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

Looking at what you are doing, it seems that [myNames] is in reality a table
containing people, and there probably is (or should be) something like a
[PersonID] which identifies a unique person and also an optional [SpouseID]
that would relate them to another Person using a self-join relationship.

create table Person
(
PersonID,
Name,
SpouseID
)

select
Person.Name,
Spouse.Name as SpouseName
from Person
left join Person as Spouse
on Spouse.PersonID = Person.SpouseID

It is is important that do you a left outer join back to the alias Spouse,
becuase some people won't have a spouse, or at least not a spouse contained
on file in the Person table.

Name Spouse
--------- -----------
Jim Fields Sally Fields
Sally Fields Jim Fields
Greg Jingle NULL


"Pinky_n_need_of_a_brian" wrote:

> I am trying to combine two rows out of a table into one single row with
> two fields.
>
> Here is my example.
>
> Select * from myNames
> {results}
> Name: Joe Done (row 1)
> Name: Jane DoMe (row 2)
>
> Name: John Snake (row1)
> Name: Katy Pipes (row2)
>
>
> {desired results}
>
> Husband: Joe Done Wife: Jane DoME (row 1)
> Husband: John Snake Wife: Katy Pipes (row 2)
>
>
> One the values are in the desired format I would like to select them
> using the new field name
>
> Example:
>
> Select Husband, wife from Sometable
>
>
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

>> I am trying to combine two rows out of a table into one single row with two fields [sic: columns are not fields]. <<

This makes no sense; rows have no ordering by definition.

>> Here is my example. <<

Where is your DDL? What you posted is useless and vague.

>> One the values are [sic: is] in the desired format I would like to select them using the new field [sic] name <<

Where is your DDL? What you posted is useless and vague.