Question on Pivot columns [message #687877] |
Mon, 10 July 2023 00:47 |
manoj12
Messages: 210 Registered: March 2008 Location: India
|
Senior Member |
|
|
Hello Sir,
The below is my SQL as shown below
with x as (select '1000257' as ASSET_NUMBER,'GOOD' AS OBSERVATION,'CBMO_ASSESSMENT' AS METER_NAME from dual
UNION ALL
SELECT '1010257' AS ASSET_NUMBER,'BAD' AS OBSERVATION, 'SF6B_ASSESSMENT' as METER_NAME from dual
)
select ASSET_NUMBER, METER_NAME_1_OBSERVATION,METER_NAME_2_OBSERVATION from x
PIVOT(
MIN(OBSERVATION) AS OBSERVATION
for METER_NAME in
(
'CBMO_ASSESSMENT' AS METER_NAME_1 , 'SF6B_ASSESSMENT' AS METER_NAME_2
)
)
It is returning output as shown below
ASSET_NUMBER METER_NAME_1_OBSERVATION METER_NAME_2_OBSERVATION
1000257 GOOD NULL
1000257 NULL BAD
I wanted to return the output as shown below
ASSET_NUMBER METER_NAME_1_OBSERVATION METER_NAME_2_OBSERVATION
1000257 GOOD BAD
Appreciate your kind help in providing the solution for this output.
|
|
|
Re: Question on Pivot columns [message #687879 is a reply to message #687877] |
Mon, 10 July 2023 02:06 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Sun, 18 October 2020 07:32Michel Cadot wrote on Tue, 12 November 2019 07:39
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...
[Updated on: Mon, 10 July 2023 04:35] Report message to a moderator
|
|
|
Re: Question on Pivot columns [message #687881 is a reply to message #687877] |
Mon, 10 July 2023 03:15 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
orclz> with x as (select '1000257' as ASSET_NUMBER,'GOOD' AS OBSERVATION,'CBMO_ASSESSMENT' AS METER_NAME from dual
2 UNION ALL
3 SELECT '1010257' AS ASSET_NUMBER,'BAD' AS OBSERVATION, 'SF6B_ASSESSMENT' as METER_NAME from dual
4 )
5 select * from x;
ASSET_N OBSE METER_NAME
------- ---- ---------------
1000257 GOOD CBMO_ASSESSMENT
1010257 BAD SF6B_ASSESSMENT
orclz> There is nothing in common between your two rows. Are they meant to be connected in some way?
|
|
|
Re: Question on Pivot columns [message #687885 is a reply to message #687881] |
Mon, 10 July 2023 08:12 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
I'll assume asset number is '1000257' for both meters and '1010257' is just a typo. Assuming meter names are known upfront:
WITH X AS (
SELECT '1000257' AS ASSET_NUMBER,'GOOD' AS OBSERVATION,'CBMO_ASSESSMENT' AS METER_NAME FROM DUAL UNION ALL
SELECT '1000257' AS ASSET_NUMBER,'BAD' AS OBSERVATION, 'SF6B_ASSESSMENT' AS METER_NAME FROM DUAL
)
SELECT *
FROM X
PIVOT(
MAX(OBSERVATION)
FOR METER_NAME IN (
'CBMO_ASSESSMENT' METER_NAME_1_OBSERVATION,
'SF6B_ASSESSMENT' METER_NAME_2_OBSERVATION
)
)
/
ASSET_NUMBER METER_NAME_1_OBSERVATION METER_NAME_2_OBSERVATION
------------ ------------------------- -------------------------
1000257 GOOD BAD
SQL>
SY.
[Updated on: Mon, 10 July 2023 08:14] Report message to a moderator
|
|
|
|
Re: Question on Pivot columns [message #687903 is a reply to message #687886] |
Tue, 18 July 2023 13:02 |
|
mathguy
Messages: 108 Registered: January 2023
|
Senior Member |
|
|
So... what was the issue?
Is it, as I suspect, that you meant both observations to be for the same ASSET_NUMBER, and the data you used for testing inadvertently had different values?
Otherwise (if the data was in fact meant to be for different assets), then your "desired result" doesn't make sense - that's why I assume it was just a typo.
In which case, I am not sure what "solution" you are talking about. Pointing out a simply typo is not a "solution".
[Updated on: Tue, 18 July 2023 13:03] Report message to a moderator
|
|
|