quote:

---

Originally posted by aakcse

Hi all,

I have a table1 & table2 as below, I want to transpose data from table1 to table two as below, could you plz help me in writing a stored proc to carry out this.

table1

---

unique_id tab_nm field1 field2 field3  fvalue1 fvalue2 fvalue3
---------------------------------------------------------------------
1            tab1   eno    ename   seq     121     aak     1
2            tab1   eno    ename           122     shb
3            tab1   acode                  INTO          
4            tab2   eno    ename   seq     123     sob      1

like wise many records

 

[/hr]

---

Table 2

ch_id rec_id  rec_no  field_nm new_val eno  tab_name
----------------------------------------------
1     1       1       eno      121     121    tab1
2     1       2       ename    aak     121    tab1
3     1       3       seq      1       121    tab1

4     2       1       eno      122     122    tab1
5     2       2       ename    shb     122    tab1

6     3       1       acode    INTO           tab1


7     1       1       eno      123     123    tab2
8     1       2       ename    sob     123    tab2
9     1       3       seq      1       123    tab2

[/hr]

---

quote:

---

Originally posted by ra.shinde

SELECT rank() over (order by rec_id,rec_no),*
FROM
(
SELECT unique_id as rec_id, 1 as rec_no, field1 as field_nm,fvalue1 as new_val,
CASE WHEN field1 = 'eno' THEN fvalue1 else '' END as ENO,tab_nm as tab_name
FROM @TAB1
union
SELECT unique_id as rec_id,2 as rec_no, field2 as field_nm,fvalue2 as new_val,
CASE WHEN field1 = 'eno' THEN fvalue1 else '' END as ENO,tab_nm as tab_name
FROM @TAB1 where field2 <> ''
union
SELECT unique_id as rec_id,3 as rec_no, field3 as field_nm,fvalue3 as new_val,
CASE WHEN field1 = 'eno' THEN fvalue1 else '' END as ENO,tab_nm as tab_name
FROM @TAB1 where field3 <> ''

)a



Rahul Shinde

---

quote:

---

Originally posted by ra.shinde

it will work but You will need to do some modifications in it

Rahul Shinde

---

quote:

---

Originally posted by ra.shinde

While writing the quey, I assumed few things;
1. First field will never be empty.
If it can be empty, add the where condition to first select also.
2. I am expecting eno to apper in first field only.
If it can appear in other fields also, Please modify the case statement acco to add more cases for other fields. But it will remain same for all statements.

Rahul Shinde

---