0

I have table data like 

value   pvalue  value_type
an1001  bk1001  1
an1002  null    1
an1003  null    1
an1004  bk1002  1
bk1001  ck1001  2
bk1002  ck1002  2
ck1001  MG1001  3
ck1002  null    3

I m expecting result like 

value   pvalue1 pvalue2 pvalue2
an1001  bk1001  ck1001  MG1001
an1002  bk1002  ck1002  
an1003          
an1004

is there any way to write queries where i can avoid left outer join or inner join rather that i can use inline queires

ashish
  • 239
  • 2
  • 6
  • 13
  • Which version of MySQL Do you use? https://stackoverflow.com/questions/7674786/mysql-pivot-table –  May 14 '18 at 05:12
  • version 5.5, when i m using left outer join i'm getting different result set in first column, for ex: if value type 1 is having 4 result set based on this i m using left outer join then result set in deifferent – ashish May 14 '18 at 05:16
  • As you mentioned "can avoid left outer join or inner join", sounds like you have to expected result using both of joins, can you update that SQL, how you did. Still am not clear with your expected output. –  May 14 '18 at 05:43
  • This is a hierachic query with a pivoted result. MySQL 5.5 is not up to such task. Use the programming language of your app or Website instead. – Thorsten Kettner May 14 '18 at 06:07
  • select a1.value,a1.pvalue as pvalue1,a2.pvalue as pvalue2,a3.pvalue as pvalue3 from testtable a1 left join (select value,pvalue from testtable where value_type = 2) a2 on a1.pvalue = a2.value left join (select value,pvalue from testtable where value_type = 3) a3 on a2.pvalue = a3.value where a1.value_type = 1 order by 1; – ashish May 14 '18 at 06:22
  • i m using above query, with limited set of record it is working fine. i m searching any other way where i can use inline query rather than left outer join – ashish May 14 '18 at 06:23
  • @ThorstenKettner yes i thought of that as it is available in oracle but not in mysql with this version, so trying to figure out solution without using left outer join, please suggest if you have any idea for the same in sql itself – ashish May 14 '18 at 06:25
  • @ThorstenKettner : i've version 5.7, will it work with hhierachic query with a pivoted result? – ashish May 14 '18 at 06:27
  • Actually this is a bad task for SQL in general. MySQL features recursive queries as of version 8.0. As to pivoting: in SQL you must know the columns you select when writing the query. In your case you select three pvalue columns. Your query is already the best solution for this in my opinion. For a dynamic number of columns this is rather a task for a programming language. – Thorsten Kettner May 14 '18 at 06:42

1 Answers1

1

You can use something like the following query. Please mind the syntax errors, if any.

select value,
     max(case when value_type = 1 then pvalue else null end) as pvalue1,
     max(case when value_type = 2 then pvalue else null end) as pvalue2,
     max(case when value_type = 3 then pvalue else null end) as pvalue3
from table
group by value;
pkgajulapalli
  • 1,066
  • 3
  • 20
  • 44