/* How to transform table like
 +-----+-----+
 | grp | txt |
 +-----|-----+
 |  A  | aaa |
 |  A  | bbb |
 |  B  | ccc |
 +-----+-----+
  into table like this:
 +-----+------+------+
 | grp | txt1 | txt2 |
 +-----+------+------+
 |  A  |  aaa |  bbb |
 |  B  |  ccc |      |
 +-----+------+------+

 ESSENTIAL!:
 All rows in the source table should have a clear ordering.
 In the example group A has two rows, data from which row should go into txt1 and which into txt2?
*/

-- sample data
create table #source(
	grp  char(1) not null,
	txt  varchar(3) not null
)
go
insert into #source values('A', 'aaa')
insert into #source values('A', 'bbb')
insert into #source values('B', 'ccc')
go


-- first step: create a temporary table where each row in a group has a number
select *,
	pos = ( select count(*)+1 from #source s2 where s2.grp=s1.grp and s2.txt<s1.txt )
into #numbered_source
from #source s1
go

-- second step: create the target table
create table #target (
	grp  char(1) not null,
	txt1 varchar(3) null,
	txt2 varchar(3) null
)
go

-- third step: fill target with primary data
insert into #target(grp)
select grp
from #numbered_source
where pos=1

-- last step
update #target set txt1=txt from #numbered_source where #target.grp=#numbered_source.grp and pos=1
update #target set txt2=txt from #numbered_source where #target.grp=#numbered_source.grp and pos=2

-- final result
select * from #target

-- All rows in the source table should have a clear ordering.