CODEWITHSUNDEEP
sqlsql-serverpivot
Abana Clara
Abana Clara

Reputation: 4650

Rotate rows into columns with column names not coming from the row

I've looked at some answers but none of them seem to be applicable to me.

Basically I have this result set:

RowNo |  Id  |  OrderNo  |
  1      101       1
  2      101       10

I just want to convert this to

|  Id  |  OrderNo_0  | OrderNo_1 |
  101       1             10

I know I should probably use PIVOT. But the syntax is just not clear to me.

The order numbers are always two. To make things clearer

Upvotes: 0

Views: 80

Answers (3)

Dale K
Dale K

Reputation: 27202

And if you want to use PIVOT then the following works with the data provided:

declare @Orders table (RowNo int, Id int, OrderNo int)

insert into @Orders (RowNo, Id, OrderNo)
  select 1, 101, 1 union all select 2, 101, 10

select Id, [1] OrderNo_0, [2] OrderNo_1
from (
  select RowNo, Id, OrderNo
 from @Orders
) SourceTable
pivot (
  sum(OrderNo)
  for RowNo in ([1],[2])
) as PivotTable

Reference: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

Note: To build each row in the result set the pivot function is grouping by the columns not begin pivoted. Therefore you need an aggregate function on the column that is being pivoted. You won't notice it in this instance because you have unique rows to start with - but if you had multiple rows with the RowNo and Id you would then find the aggregation comes into play.

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

From your sample data, simplest you can try to use min and MAX function.

SELECT Id,min(OrderNo) OrderNo_0,MAX(OrderNo) OrderNo_1
FROM T
GROUP BY Id

Upvotes: 1

Matt Shepherd
Matt Shepherd

Reputation: 803

As you say there are only ever two order numbers per ID, you could join the results set to itself on the ID column. For the purposes of the example below, I'm assuming your results set is merely selecting from a single Orders table, but it should be easy enough to replace this with your existing query.

SELECT o1.ID, o1.OrderNo AS [OrderNo_0], o2.OrderNo AS [OrderNo_1] 
FROM Orders AS o1 
INNER JOIN Orders AS o2 
ON (o1.ID = o2.ID AND o1.OrderNo <> o2.OrderNo)

Upvotes: 2

Related Questions