Sql Statement for converting table into another form

Transact-SQL
1
INO opt1 opt2 opt3 p1 p2 p3
1001 aa bb cc 100 200 300
1002 bb cc dd 400 600 700
Converting above table in this format given below
INO opt p
1001 aa 100
1001 bb 200
1001 cc 300
1002 bb 400
1002 cc 600
1002 dd 700
2

Don't have any usable data to test with, but I think this should do it:

SELECT INO, CA.*
FROM dbo.table_name
CROSS APPLY ( VALUES(opt1, p1),(opt2, p2), (opt2, p3) ) test_data(opt, p)
3

Thanks a lot ill check it.

4

hi

i tried to do this

please click arrow to the left for drop create data .. 
drop table #data 
go 


create table #data 
(
INO int ,
opt1 varchar(5) , 
opt2 varchar(5) , 
opt3 varchar(5) , 
p1 int ,
p2 int,
p3 int 
)
go 

insert into #data select 1001,	'aa','bb','cc',100,	200, 300
insert into #data select 1002,	'bb','cc','dd',400,	600, 700
go 

select * from #data
go

image
image1092×211 3.87 KB

hope this helps :slight_smile: :slight_smile:

please click arrow to the left for SQL .. 
select 'SQL Output',* from 
(
select ino , opt1,p1 from #data 
union all 
select ino , opt2,p2  from #data 
union  all 
select ino , opt3,p3  from #data 
) a 
order by 1,2,3

image

5

or

create table #pnaz(INOT int, opt1 varchar(50),
opt2 varchar(50), opt3 varchar(50), p1 int, p2 int, p3 int)



insert into #pnaz
select 1001,	'aa',	'bb',	'cc',	100,	200,	300 union
select 1002,	'bb',	'cc',	'dd',	400,	600,	700

SELECT INOT,
       [Option],
       Point
  FROM
  (
	SELECT INOT, opt1, opt2, opt3, p1, p2, p3
	FROM #pnaz
) src
UNPIVOT
(
  [Option] FOR Options in (opt1, opt2, opt3)
) pvt1

UNPIVOT
(
	Point FOR Points in (p1, p2, p3)
) pvt2
WHERE RIGHT(Options,1) = RIGHT(Points,1)



drop table #pnaz