Transpose rows to columns where transposed column changes based on another column

Question

I want to transpose the rows to columns using Pivot function in Oracle and/or SQL Server using Pivot function. My use case is very similar to this Efficiently convert rows to columns in sql server However, I am organizing data by specific data type (below StringValue and NumericValue is shown).

This is my example:

   ----------------------------------------------------------------------
   | Id | Person_ID | ColumnName     | StringValue  | NumericValue      |
   ----------------------------------------------------------------------
   | 1  |     1     |  FirstName     |  John        |    (null)         |
   | 2  |     1     |  Amount        |  (null)      |     100           |
   | 3  |     1     |  PostalCode    |  (null)      |    112334         |
   | 4  |     1     |  LastName      |  Smith       |      (null)       |
   | 5  |     1     |  AccountNumber |   (null)     |     123456        |
   ----------------------------------------------------------------------

This is my result:

---------------------------------------------------------------------
| FirstName  |Amount|   PostalCode   |   LastName  |  AccountNumber |
---------------------------------------------------------------------
| John       | 100  |   112334       |   Smith     |  123456        |
---------------------------------------------------------------------

How can I build the SQL Query?

I have already tried using MAX(DECODE()) and CASE statement in Oracle. However the performance is very poor. Looking to see if Pivot function in Oracle and/or SQL server can do this faster. Or should I go to single column value?

Answer 1

Below code will satisfy your requirement

Create   table #test
(id int,
person_id int,
ColumnName varchar(50),
StringValue varchar(50),
numericValue varchar(50)
)

insert into #test values (1,1,'FirstName','John',null)
insert into #test values (2,1,'Amount',null,'100')
insert into #test values (3,1,'PostalCode',null,'112334')
insert into #test values (4,1,'LastName','Smith',null)
insert into #test values (5,1,'AccountNumber',null,'123456')

--select * from  #test


Declare @Para varchar(max)='',
 @Para1 varchar(max)='',
@main varchar(max)=''

select  @Para +=  ','+QUOTENAME(ColumnName)
 from (select distinct ColumnName from #test) as P 
set @Para1= stuff(@para ,1,1,'')
print @Para1

set @main ='select * from (
select  coalesce(StringValue,numericValue) as Val,ColumnName from #test) as Main
pivot
(
min(val) for  ColumnName in ('+@Para1+')
) as pvt'

Exec(@main)