Data normalization: switch column and rows of data

Question

I have a table like this:

Table 1
ID   Data
1    Name
2    Adam
3    SEX
4    Male
5    AGE
6    27
7    Name
8    Bob
9    SEX
10   Male
11   AGE
12   28

And I'd like to convert it into following format

ID  NAME  SEX   AGE
1   Adam  Male   27
2   Bob   Male   28

How to do this in Sql Server? Thanks

Answer 1

Here you go:

insert into table2
select t1.Data "name", t2.Data "sex", t3.Data "Age"
from table1 t1,
table1 t2,
table1 t3
where (t1.ID + 4) % 6 = 0
and (t2.ID + 2) % 6 = 0
and t3.ID % 6 = 0
and t1.ID + 2 = t2.ID
and t1.ID + 4 = t3.ID

Adam MALE 27

Bob MALE 28

Alice FEMALE 99

I added an extra record to double check that it will work beyond the first two iterations of your data. And BTW, you would not be able to use PIVOT for this as your data is not normalized