0

Here is my table

Equipmentid    Application   Value
===========    ===========   =====
k001              THK         True
k001              BHK         False
k001              KHK         True

Here is what I expected:

Equipmentid    THK   BHK    KHK
===========    ===   ===    ===
k001           True  False  True

I'm trying to use normal transpose Oracle using max decode but in the end need to mention AS [tablename], I want to dynamically create row to column base on row name, this database will involve very much application. Thank guys

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kedai Ezbakr
  • 15
  • 6
  • You may be misusing the database here. Retrieve the data and do it in you client side, either PL/SQL or any other OCI methods. – Maheswaran Ravisankar Dec 15 '15 at 06:26
  • @MaheswaranRavisankar , this is view i got it from database , no chance to solve this ? – Kedai Ezbakr Dec 15 '15 at 06:31
  • You'd need to dynamically construct the SQL statement. Something like http://stackoverflow.com/questions/32451072/how-to-make-dynamic-pivot-in-oracle-pl-sql That's much more complicated plus it means that you have to read the data once, construct your query, and read the data again which isn't terribly efficient. – Justin Cave Dec 15 '15 at 06:42
  • hi @JustinCave , i got this database from view , and i need to categorized each equipmentid with application that true or false . about 3000 equipment i need to insert into. – Kedai Ezbakr Dec 15 '15 at 06:49
  • If you want the number and name of columns to be dynamic, you'd need dynamic SQL. If you want the number and name of columns to differ for different rows then, yes, you'd need multiple queries. If you want to create a column for any `application` that exists in the table, you can dynamically create a single SQL statement – Justin Cave Dec 15 '15 at 06:52
  • @JustinCave, any idea? im stuck in here , lot equipment and lot application with different arrange each application. – Kedai Ezbakr Dec 15 '15 at 06:55
  • It's a bit hard to guess exactly what you want when your example shows a single `equipmentID` but your comments talk about having lots of different `equipmentID` values with many different `application` values. If you want one column of output for every distinct `application` and one row for each `equipmentID` with lots of NULL values, then you're looking at dynamically constructing a SQL statement and this is a duplicate of the question that I linked to. If you don't believe it is a duplicate, please help us understand the difference between this question and that one. – Justin Cave Dec 15 '15 at 07:34

3 Answers3

2

Hi try using PIVOT,

WITH x(equipment_id, application, VALUE ) 
     AS (SELECT 'k001', 'THK', 'TRUE' FROM DUAL UNION ALL
         SELECT 'k001', 'BHK', 'FALSE' FROM DUAL UNION ALL
         SELECT 'k001', 'KHK', 'TRUE' FROM DUAL  UNION ALL
         SELECT 'k002', 'KHK', 'FALSE' FROM DUAL UNION ALL
         SELECT 'k002', 'THK', 'FALSE' FROM DUAL UNION ALL
         SELECT 'k002', 'BHK', 'FALSE' FROM DUAL )

  SELECT * FROM
(
  SELECT equipment_id, value, application
  FROM x
)
PIVOT
(
  MAX(value)
  FOR application IN ('THK', 'BHK', 'KHK')
) order by equipment_id;

Alternatively, if you want to have dynamic column, you can use subquery in the IN clause then use PIVOT XML,but result will be of XML TYPE which i dont know how to extract the values.(just saying) if you want to know more about how to do it dynamically with pl/sql. Read here .Here's the source

  SELECT * FROM
(
  SELECT equipment_id, value, application
  FROM x
)
PIVOT XML
(
  MAX(value)
  FOR application IN (SELECT DISTINCT application from x)
) order by equipment_id;
brenners1302
  • 1,440
  • 10
  • 18
1

Try this one.

SELECT EQUIPMENTID,
max(case  when  APPLICATION = 'THK' then VALUE end) as "THK",
max(case  when  APPLICATION = 'BHK' then VALUE end) as "BHK",
max(case  when  APPLICATION = 'KHK' then VALUE end) as "KHK"
FROM [tablename]
group by EQUIPMENTID;
Mikhailov Valentin
  • 1,092
  • 3
  • 16
  • 23
  • hi @MikhailovValentine , this is what im doing before , but if i have more 50 application, do i need to write long code ? – Kedai Ezbakr Dec 15 '15 at 06:54
0

You can left join in this case.

SELECT t1.Equipmentid, t2.Value AS 'THK', t3.Value AS 'BHK', t4.Value AS 'KHK' FROM TABLE t1
     LEFT JOIN (SELECT Equipmentid, Value FROM TABLE WHERE Application = 'THK') AS t2 ON (t1.Equipmentid = t2.Equipmentid)
     LEFT JOIN (SELECT Equipmentid, Value FROM TABLE WHERE Application = 'BHK') AS t3 ON (t1.Equipmentid = t3.Equipmentid)
     LEFT JOIN (SELECT Equipmentid, Value FROM TABLE WHERE Application = 'KHK') AS t4 ON (t1.Equipmentid = t4.Equipmentid)

Even though it can be solve. But this method is not good in my opinion. Hope it help you anyway

Dean
  • 668
  • 12
  • 32
  • hi @dean , thank for your response , but for you information, i got many application each equipmentid. – Kedai Ezbakr Dec 15 '15 at 06:46
  • then i advice you to use brenners's answer, but instead of insert FOR statement 1 by 1 ... try replace it with FOR application IN (' + @ColumnName + ')) – Dean Dec 15 '15 at 06:54
  • @ColumnName can be declare as SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Value) FROM (SELECT DISTINCT Value FROM TABLE Where Equipmentid = 'k001') – Dean Dec 15 '15 at 06:56
  • thank a lot for helping, but later on this database will involved 3000 id , do i need to put all id there? – Kedai Ezbakr Dec 15 '15 at 06:58
  • sry for lated reply... by using IN(@variable) the @variable is already select all possible Application that you have in your table... therefore you doesn't really need to care when you add new or delete Application – Dean Dec 15 '15 at 10:15