Convert Table Format

Transact-SQL
1
InqNo EqNo opt Price
A001 E01 ABC 34
A001 E01 EFG 43
A001 E01 HIJ 54
A001 E02 ABC 89
A001 E02 EFG 54
A001 E02 HIj 78
A001 E03 ABC 45
A001 E03 EFG 43
A001 E03 HIj 60
Convert To:-
Dynamic Dynamic Dynamic Dynamic
InqNo Opt E01 E02 E03 E04
A001 ABC 34 89 45
A001 EFG 43 54 43
A001 HIJ 54 78 60
2

Please provide your data in Ddl and dml format

Create table sample(InqNo varchar(10), EaNo varchar(10), opt varchar(10), Price money)

insert into sample
select 'A001', 'E01', 'ABC' 34 union
select 'A01', 

``

easier to work with real data rather than a picture/
3

Hi

I hope this helps
:slight_smile:
:slight_smile:

drop create data ... 
drop table #abc 

create table #abc 
(
InqNo varchar(100) , 
EqNo varchar(100) , 
opt varchar(100) , 
Price int 
)
go 

insert into #abc select 'A001','E01','ABC',34
insert into #abc select 'A001','E01','EFG',43
insert into #abc select 'A001','E01','HIJ',54
insert into #abc select 'A001','E02','ABC',89
insert into #abc select 'A001','E02','EFG',54
insert into #abc select 'A001','E02','HIj',78
insert into #abc select 'A001','E03','ABC',45
insert into #abc select 'A001','E03','EFG',43
insert into #abc select 'A001','E03','HIj',60
go
SQL 
DECLARE @columns NVARCHAR(max), 
        @sql     NVARCHAR(max); 

SET @columns = N''; 

SELECT @columns += N', p.' + Quotename(eqno) 
FROM   (SELECT DISTINCT eqno 
        FROM   #abc) AS x; 

-- print @columns  
SET @sql = N' SELECT InqNo,Opt,' + Stuff(@columns, 1, 2, '') 
           + ' FROM (   SELECT InqNo , EqNo , opt , Price from #abc   ) AS j PIVOT (   MAX(Price) FOR EqNo IN (' 
           + Stuff(Replace(@columns, ', p.[', ',['), 1, 1, '') 
           + ') ) AS p;'; 

PRINT @sql; 

EXEC Sp_executesql 
  @sql;
Result

image

4

Thanks a lot its working. What if i want to give condition for InqNo. How we can achieve this approach.
For example if InqNo is A001 there is result for E01,E02,E03(only three Eq)

For Inq A003 for example there is result for E04,E05,E06(only these to be displayed)

InqNo Opt E01 E02 E03
A001 ABC 34 89 45
A001 EFG

InqNo Opt E04 E05 E06
A003 --

6

Hi

Please find what I tried
What if i want to give condition for InqNo.

thanks
:slight_smile:
:slight_smile:

drop create data .. 
drop table #abc 

create table #abc 
(
InqNo varchar(100) , 
EqNo varchar(100) , 
opt varchar(100) , 
Price int 
)
go 

insert into #abc select 'A001','E01','ABC',34
insert into #abc select 'A001','E01','EFG',43
insert into #abc select 'A001','E01','HIJ',54
insert into #abc select 'A001','E02','ABC',89
insert into #abc select 'A001','E02','EFG',54
insert into #abc select 'A001','E02','HIj',78
insert into #abc select 'A001','E03','ABC',45
insert into #abc select 'A001','E03','EFG',43
insert into #abc select 'A001','E03','HIj',60
go

SELECT * from #abc 
go
SQL .. 
DECLARE @columns NVARCHAR(max), 
        @sql     NVARCHAR(max); 

Declare @inqno varchar(10) = 'A001'

SET @columns = N''; 

SELECT @columns += N', p.' + Quotename(eqno) 
FROM   (SELECT DISTINCT eqno 
        FROM   #abc where inqno = @inqno) AS x; 

print @columns  

SET @sql = N' SELECT InqNo,Opt,' + Stuff(@columns, 1, 2, '') 
           + ' FROM (   SELECT InqNo , EqNo , opt , Price from #abc  
		   where inqno = '''+@inqno+'''  ) AS j PIVOT (   MAX(Price) FOR EqNo IN (' 
           + Stuff(Replace(@columns, ', p.[', ',['), 1, 1, '') 
           + ') ) AS p;'; 

PRINT @sql; 

EXEC Sp_executesql 
  @sql;
Result

image

7

Thanks it worked.