CODEWITHSUNDEEP
sqloracle-databaseunpivot
NIKHIL KULSHRESTHA
NIKHIL KULSHRESTHA

Reputation: 134

Columns data into rows in oracle

enter image description here

Data is present in the screenshot as table format. I want to convert into desired format as mentioned below.

Table describe :

Table A
(branch_code, branch_name, branch_state, hol1, hol2, hol3....hol100)

Expected Output

TAMF046  14/01/2021
TAMF046  15/01/2021
TAMF046  26/01/2021
KERF047  26/01/2021
KERF047  11/03/2021
KERF047  02/04/2021

Upvotes: 0

Views: 82

Answers (3)

NIKHIL KULSHRESTHA
NIKHIL KULSHRESTHA

Reputation: 134

 WITH HOLIDAY 
AS ( 
    SELECT BRANCH_CODE,HOL1,HOL2,HOL3,HOL4,HOL5,HOL6,HOL7,HOL8,HOL9,HOL10,HOL11,HOL12,HOL13,HOL14,HOL15,HOL16,HOL17,HOL18,HOL19,HOL20,HOL21,HOL22,HOL23,HOL24,HOL25,HOL26,HOL27,HOL28,HOL29,HOL30,HOL31,HOL32,HOL33,HOL34,HOL35,HOL36,HOL37,HOL38,HOL39,HOL40,HOL41,HOL42,HOL43,HOL44,HOL45,HOL46,HOL47,HOL48,HOL49,HOL50,HOL51,HOL52,HOL53,HOL54,HOL55,HOL56,HOL57,HOL58,HOL59,HOL60,HOL61,HOL62,HOL63,HOL64,HOL65,HOL66,HOL67,HOL68,HOL69,HOL70,HOL71,HOL72,HOL73,HOL74,HOL75,HOL76,HOL77,HOL78,HOL79,HOL80,HOL81,HOL82,HOL83,HOL84,HOL85,HOL86,HOL87,HOL88,HOL89,HOL90,HOL91,HOL92,HOL93,HOL94,HOL95,HOL96,HOL97,HOL98,HOL99,HOL100 
      FROM CUST_HOLIDAY_MASTER 
      WHERE BRANCH_CODE = I.BRANCH_CODE
    )
SELECT  BRANCH_CODE , COLVALUE FROM abc

unpivot
( colvalue for col in (HOL1,HOL2,HOL3,HOL4,HOL5,HOL6,HOL7,HOL8,HOL9,HOL10,HOL11,HOL12,HOL13,HOL14,HOL15,HOL16,HOL17,HOL18,HOL19,HOL20,HOL21,HOL22,HOL23,HOL24,HOL25,HOL26,HOL27,HOL28,HOL29,HOL30,HOL31,HOL32,HOL33,HOL34,HOL35,HOL36,HOL37,HOL38,HOL39,HOL40,HOL41,HOL42,HOL43,HOL44,HOL45,HOL46,HOL47,HOL48,HOL49,HOL50,HOL51,HOL52,HOL53,HOL54,HOL55,HOL56,HOL57,HOL58,HOL59,HOL60,HOL61,HOL62,HOL63,HOL64,HOL65,HOL66,HOL67,HOL68,HOL69,HOL70,HOL71,HOL72,HOL73,HOL74,HOL75,HOL76,HOL77,HOL78,HOL79,HOL80,HOL81,HOL82,HOL83,HOL84,HOL85,HOL86,HOL87,HOL88,HOL89,HOL90,HOL91,HOL92,HOL93,HOL94,HOL95,HOL96,HOL97,HOL98,HOL99,HOL100) 
)

WHERE COLVALUE IS NOT NULL;

This is the way which i try to this but if anyone has any other way using dynamic query. Please share your views,

Upvotes: 0

Petr
Petr

Reputation: 540

This is exactly what UNPIVOT is for

with t(id, c1, c2, c3) as (
  select 1, 'a', 'b', 'c' from dual union all
  select 2, 'aa', 'bb', 'cc' from dual
)
select *
from t
unpivot (
  val
  for col in (
    c1 as 'A1',
    c2 as 'A2',
    c3 as 'A3'
  )
)

val is the new column that will contain values from columns c1 c2 c3.

col is the new column that will contain the name of the column from where the val comes from.

A1 A2 A3 are the values you want to fill in the col for each unpivoted column (these aliases can be omitted if you are ok with the original column names).

Upvotes: 2

Peter Dongan
Peter Dongan

Reputation: 2306

Use union

Eg:

select Branch_Code, Hol1 from MyTable
union
select Branch_Code, Hol2 from MyTable

etc

Upvotes: 0

Related Questions