Nsryjdtyk

I need to select data from different rows returning one row.
I have table like this:

SNO USEFUL COUNTER PINCODE SUBSNO    DATA                            REFERENCE

--- ------ ------- ------- ------    -----------------------------   ---------

 1    Y     null    504293  null     504293                             Sl.No 

 2    null  null    504293  null     1                                  null

 3    null  null    504293  null     iciseva00031                       null

 4    null  null    504293  null     SANTHOSH KUMAR                     null

 5    null  null    504293  null     SANTHOSH MANTHENA                  null

 6    null  null    504293  null     12-52  BRAHMAN WADA                null

 7    null  null    504293  null     ASIFABAD                           null

 8    null  null    504293  null     Andhra Pradesh                     null

 9    null  null    504293  null     9248859222                         null

10    null  null    504293  null     2                                  null

11    null  null    504293  null     WSGLDPL12415                       null

12    null  null    504293  null     SIDDHARTHA COMMUNICATIONS          null

13    null  null    504293  null     MASADE SATISH                      null

14    null  null    504293  null     HNO10-143POSTOFFICEROADBAPUNAGAR   null

                                     ASIFABADDISTKUMRAMBHEEMASIFABAD 

15    null  null    504293  null     ADILABAD                           null

16    null  null    504293  null     ANDHRA PRADESH                     null

17    null  null    504293  null     9059187009                         null

And I have to return output like this:

PINCODE SUBSNO USER          COMPANY       AGENT            ADDRESS CITY         STATE        PHONE

------- ------ ----          -------       -----            ------- ----         -----        -----

504293  1      iciseva00031  SANTHOSHKUMAR SANTHOSHMANTHENA 12-52   BRAHMAN WADA ASIFABAD  Andhra Pradesh 9248859222

Please help me.

" I have to show output table like this:"

It's easy enough to grab that one row (use an editor that has regex support to help wrangle the repetition):

select t1.data as PINCODE

        , t2.data as SUBSNO

        , t3.data as USER

        , t4.data as COMPANY

        , t5.data as AGENT

        , t6.data as ADDRESS

        , t7.data as CITY

        , t8.data as STATE

        , t9.data as PHONE

 from ( select PINCODE, DATA from your_table where PINCODE = DATA ) t1                            

    left join ( select PINCODE, DATA from your_table where SNO = 2) t2

        on t2.pincode = t1.pincode                            

    left join ( select PINCODE, DATA from your_table where SNO = 3) t3

        on t3.pincode = t1.pincode                             

    left join ( select PINCODE, DATA from your_table where SNO = 4) t4

        on t4.pincode = t1.pincode                             

    left join ( select PINCODE, DATA from your_table where SNO = 5) t5

        on t5.pincode = t1.pincode                             

    left join ( select PINCODE, DATA from your_table where SNO = 6) t6

        on t6.pincode = t1.pincode                             

    left join ( select PINCODE, DATA from your_table where SNO = 7) t7

        on t7.pincode = t1.pincode                             

    left join ( select PINCODE, DATA from your_table where SNO = 8) t8

        on t8.pincode = t1.pincode                             

    left join ( select PINCODE, DATA from your_table where SNO = 9) t9

        on t9.pincode = t1.pincode           

where t1.pincode = '504293'

/

Of course, this is probably not what you need. No doubt you want to have all the additional rows - sno >= 10 too. Simple: just keep repeating the above process for as many columns as you need.

Notes.

1. 
   Do you have to hard code the column names in the projection? Yes. The presented value table has no attribute type indicator so there's no way of knowing what the column represents. So you need to inject that missing intelligence into the query yourself.


2. 
   If it turns out the table does have an attribute type column, can you use that to label the columns in the projection? Yes, but it means dynamically generating the query.


3. 
   What happens if another PINCODE holds the columns in a different order? How will you tell? Without an attribute type indicator you don't know what any column is supposed to be, all you have to go on is the presumed precedence represented by your sample output.


4. 
   All those joins: won't the performance suck? Yes, yes it probably will.

Unfortunately for you there is no simple. elegant, performative solution because the source data is a car crash.

These generic data storage tables are seductive for developers writing data input routines. They get to stash the data in a very simple manner and don't have to worry themselves with data modelling and all that tedious stuff. Plus it's super flexible and future-proof. But what they have actually done is chalked up a massive pile of technical debt, which has to be paid off by the developers who need to get the data out.