1

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?

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
Programmer24
  • 25
  • 3
  • Which DBMS are you actually using? The answer will be different depending on your response. I would use conditional aggregation here instead of a PIVOT. The syntax is far less obtuse and gives you some flexibility to do switch the column being returned like you need here. – Sean Lange Jun 13 '19 at 20:24
  • try pivot sql query: https://hasanmahmood.com/2019/03/19/basic-pivot-sql-query/ – Hasan Mahmood Jun 13 '19 at 20:42
  • @HasanMahmood a basic pivot is not going to work here. They need to choose between one of two columns to return based on the value of another column. And my fear is they are using an EAV so they need to add a layer of dynamic sql over the top to make this extra painful. – Sean Lange Jun 13 '19 at 20:47
  • @SeanLange I am using Oracle at the moment. Can you give me the example for conditional aggregation for Oracle? – Programmer24 Jun 13 '19 at 22:06
  • Sorry I can barely spell Oracle and have no way to test my code. I could give you a sql server example and it would probably work in Oracle. – Sean Lange Jun 14 '19 at 13:25

1 Answers1

0

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)
Raj
  • 16
  • 1
  • 4
  • Assuming that the numericValue column in the actual table is a numeric datatype you would have to convert numericValue inside the coalesce to a varchar datatype or this will crash. And sadly this is a good example for sql server, the OP has clarified they are using Oracle. – Sean Lange Jun 14 '19 at 13:21
  • Thanks Sean and Raj. I implemented same in Oracle and it gives me the result I need. Performance is better than DECODE or CASE but still not great. But thanks for your help. – Programmer24 Jun 14 '19 at 15:25