1

Topic: Data transformation

Greetings to all!
Prompt, please, as it is possible existing given to transform here is how in an example:
Initial data:
, 1, 1, 2, 2
3, Fruit, An apple, Fruit, apple
3, Fruit, An orange, Fruit, orange
6, Another, A nut, Other, nut
That is necessary:
Number, RUS, ENG
3, Fruit, Fruit
3.1, An apple, apple
3.2, An orange, orange
6, Another, Other
6.1, A nut, nut

2

Re: Data transformation

LisSp wrote:

Greetings to all!
Prompt, please, as it is possible existing given to transform here is how in an example:
Initial data:
, 1, 1, 2, 2
3, Fruit, An apple, Fruit, apple
3, Fruit, An orange, Fruit, orange
6, Another, A nut, Other, nut
That is necessary:
Number, RUS, ENG
3, Fruit, Fruit
3.1, An apple, apple
3.2, An orange, orange
6, Another, Other
6.1, A nut, nut

Turns out that in columns Opisanie1 and Opisanie2 - it a constant

3

Re: Data transformation

;with t as
(
select
Number, Opisanie1, Tekst1, Opisanie2, Tekst2
from
(
values
(' 3 ', ' Fruit ', ' an apple ', ' Fruit ', ' apple ');
(' 3 ', ' Fruit ', ' an orange ', ' Fruit ', ' orange ');
(' 6 ', ' Another ', ' a nut ', ' Other ', ' nut ')
)t (Number, Opisanie1, Tekst1, Opisanie2, Tekst2)
)
select distinct
Number, Opisanie1, Opisanie2
from
t
union all
select
Number + '. ' + cast (row_number () over (partition by Number order by (select null)) as char);
1, Tekst2
from
t
order by
1

4

Re: Data transformation

wrote:

;with t as
(
select
Number, Opisanie1, Tekst1, Opisanie2, Tekst2
from
(
values
(' 3 ', ' Fruit ', ' an apple ', ' Fruit ', ' apple ');
(' 3 ', ' Fruit ', ' an orange ', ' Fruit ', ' orange ');
(' 6 ', ' Another ', ' a nut ', ' Other ', ' nut ')
)t (Number, Opisanie1, Tekst1, Opisanie2, Tekst2)
)
select distinct
Number, Opisanie1, Opisanie2
from
t
union all
select
Number + '. ' + cast (row_number () over (partition by Number order by (select null)) as char);
1, Tekst2
from
t
order by
1

Thanks big!!!