I need to take 2 rows of a table (it will always be 2) and have them merged into 1 row, so that these fields in the table:
BakeTime, Diameter, Size, Height
end up being something like
BakeTime1, BakeTime2, Diameter1, Diameter2, Size1, Size2, Height1, Height2
in the final result record. There is a 'unique id' field in the table, which is unique to the 2 records at a time that I want output together.
You should be able to use an inner join to generate a single row from the two records. Per your statements in the comments regarding an identifying column(CakeNo), something like below should work.
SELECT t1.BakeTime AS BakeTime1, t2.BakeTime as BakeTime2, t1.Diameter AS Diameter1, t2.Diameter AS Diameter2,
t1.Size AS Size1, t2.Size AS Size2, t1.Height AS Height1, t2.Height AS Height2
FROM YourTable t1
INNER JOIN YourTable t2 ON t2.UniqueId = t1.UniqueId AND t2.CakeNo != t1.CakeNo
WHERE t1.UniqueId = 'YourIdHere' AND RIGHT(t1.CakeNo, 1) = 'A'
DISCLAIMER: I'm not entirely sure I understand your use of CakeNo, LEFT(t1.CakeNo, 1) = '1' might be necessary in place of RIGHT(t1.CakeNo, 1) = 'A'.
If the ID are different - not clear from your question
SELECT t1.*, t2.* // yes you need to alias actual column names
FROM YourTable t1
JOIN YourTable t2
ON t1.UniqueId = 1 // id1
AND t2.UniqueId = 2 // id2
If ID are same and CakeNo are different
A lot borrowed from Matt (+1)
;WITH cte AS (
SELECT *
, ROW_NUMBER() OVER (PARTITION BY uniqueID ORDER BY CakeNo) as RowNum
FROM
TableName
--where uniqueID = @ID -- in you only need one do it here
)
SELECT
c1.BakeTime as BakeTime1
,c2.BakeTime as BakeTime2
,c1.Diameter as Diameter1
,c2.Diameter as Diameter2
,c1.Size as Size1
,c2.Size as Size2
,c1.Height as Height1
,c2.Height as Height2
FROM
cte c1
left JOIN cte c2 -- in case only one row
ON c2.RowNum = 2
AND c1.RowNum = 1
this works if the two CakeNo are the same
if more than two rows with same ID then would just get the first 2
;WITH cte AS (
SELECT
*
,LEFT(CakeNo,LEN(CakeNo) - 1) as CorrelationId
,ROW_NUMBER() OVER (PARTITION BY LEFT(CakeNo,LEN(CakeNo) - 1) ORDER BY CakeNo) as RowNum
FROM
TableName
)
SELECT
c1.BakeTime as BakeTime1
,c2.BakeTime as BakeTime2
,c1.Diameter as Diameter1
,c2.Diameter as Diameter2
,c1.Size as Size1
,c2.Size as Size2
,c1.Height as Height1
,c2.Height as Height2
FROM
cte c1
INNER JOIN cte c2
ON c1.CorrelationId = c2.CorrelationId
AND c2.RowNum = 2
WHERE
c1.RowNum = 1
Based on your comment that you can derived a correlation id from taking the 1st character of CakeNo. Because I would assume you will have more than 9 records, I thought of it as if you remove the A/B from the end you can get to your CorrelationId. I have updated my answer to reflect this.
Since you will always have only two rows try something like this
SELECT Concat(Min(baketime), ',', Max(baketime)),
Concat(Min(diameter), ',', Max(diameter)),
Concat(Min(size), ',', Max(size)),
Concat(Min(height), ',', Max(height))
FROM yourtable
If you are using anything less SQL Server 2012 then replace CONCAT with + operator to concatenate the strings
Update : Looks like you want this
SELECT Min(baketime),
Max(baketime),
Min(diameter),
Max(diameter),
Min(size),
Max(size),
Min(height),
Max(height)
FROM yourtable
