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

Pivot; possible duplicate of http://stackoverflow.com/questions/10976585/convert-rows-to-columns-using-pivot-in-mssql-when-columns-are-string-data-type — gregory, Mar 16 '17 at 04:34

score 1 · Accepted Answer · answered Mar 16 '17 at 05:10

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

vbp13 · Answer 2 · 2017-03-16T04:05:27.807

-1

create table #tt
(id int, data varchar(20))

insert #tt
(id , data)
values
(1,'Name'),
(2,'Adam'),
(3,'SEX'),
(4,'Male'),
(5,'AGE'),
(6,'27'),
(7,'Name'),
(8,'Bob'),
(9,'SEX'),
(10,'Male'),
(11,'AGE'),
(12,'28')


create table #endResult
(ID int identity(1,1), Name varchar(50),SEX VARCHAR(6), Age int)

insert #endResult
(Name, SEX, Age )
select 
        t2.data as Name
        ,t4.data as SEX
        ,t6.data as Age 
from 
        #tt t1 
join 
        #tt t2 
    on 
        t1.data = 'Name'
    and 
        t1.id+1 = t2.id
join 
        #tt t3 
    on 
        t1.id+2 = t3.id
    and
        t3.data = 'SEX' 
join 
        #tt t4 
    on 
        t1.id+3 = t4.id
join
        #tt t5 
    on
        t1.id+4 = t5.id
    and
        t5.data = 'AGE'
JOIN
        #tt t6
    on
        t1.id+5 = t6.id




 select * from #endResult

edited Mar 16 '17 at 04:05

answered Mar 16 '17 at 03:55

vbp13

1,040
1
10
20

Where'd you get t4 and t6? – Renats Stozkovs Mar 16 '17 at 04:16
@bc004346 - t4 and t6 are the same source as your t#s. They are the source for sex and age columns. While your approach is less code and seems that it will work, you are very heavily relying on the rows starting with ID 1. I know that's his example but in case this table isn't a one time issue and someone accidentally adds one row anywhere that doesn't fit the exact numerical scheme, your query will return incorrect results. – vbp13 Mar 16 '17 at 12:33
on a different note, whoever downvoted, please comment why. – vbp13 Mar 16 '17 at 12:35
Oh yeah, I missed the part downstream - din't see the scroll bar. It makes sense. I think that given the question, it is rather safe to assume that the data was probably imported from somewhere and the OP does not know of any irregularities in how it is numbered. – Renats Stozkovs Mar 16 '17 at 14:26

Data normalization: switch column and rows of data

2 Answers2