Rows in Columns (SQL)

Asked

Viewed 548 times

2

I’ve been thinking for a few days about how to make a select less verbose, turning rows into columns. Today I have it that way:

(SELECT s.code_sample
          FROM app_sample s
         WHERE s.id_analysis = a.id
           AND s.sequential_order = '001') AS code_sample_1,
       (SELECT s.code_sample
          FROM app_sample s
         WHERE s.id_analysis = a.id
           AND s.sequential_order = '002') AS code_sample_2,
       (SELECT s.code_sample
          FROM app_sample s
         WHERE s.id_analysis = a.id
           AND s.sequential_order = '003') AS code_sample_3,
       (SELECT s.code_sample
          FROM app_sample s
         WHERE s.id_analysis = a.id
           AND s.sequential_order = '004') AS code_sample_4,
       (SELECT s.code_sample
          FROM app_sample s
         WHERE s.id_analysis = a.id
           AND s.sequential_order = '005') AS code_sample_5,
       (SELECT s.code_sample
          FROM app_sample s
         WHERE s.id_analysis = a.id
           AND s.sequential_order = '006') AS code_sample_6,
       (SELECT s.code_sample
          FROM app_sample s
         WHERE s.id_analysis = a.id
           AND s.sequential_order = '007') AS code_sample_7,
       (SELECT s.code_sample
          FROM app_sample s
         WHERE s.id_analysis = a.id
           AND s.sequential_order = '008') AS code_sample_8,

The above script works, but I have two problems with it: First that there is a lot of information that are in rows and need to turn columns like this and there are only 8 columns - can vary up to 100 for each item according to its sequence. According to that the order received is not always sequential, for example ('001', '003', '040'), as it depends on the situation.

The script in this format is giant and impossible to maintain, another is that I need to order the sequence so that there is no 'gap' in the report display.

I’m open to suggestions and discussions.

Grateful.

sql postgresql report

2 answers

0


An option would be like this:

select a.id , 
sCol001.code_sample as code_sample_1, 
sCol002.code_sample as code_sample_2, 
sCol003.code_sample as code_sample_3, 
sCol004.code_sample as code_sample_4, 
sCol005.code_sample as code_sample_5, 
sCol006.code_sample as code_sample_6, 
sCol007.code_sample as code_sample_7, 
sCol008.code_sample as code_sample_8
from a 
left join app_sample sCol001 on sCol001.id_analysis = a.id and sCol001.sequential_order = '001'
left join app_sample sCol002 on sCol002.id_analysis = a.id and sCol002.sequential_order = '002'
left join app_sample sCol003 on sCol003.id_analysis = a.id and sCol003.sequential_order = '003'
left join app_sample sCol004 on sCol004.id_analysis = a.id and sCol004.sequential_order = '004'
left join app_sample sCol005 on sCol005.id_analysis = a.id and sCol005.sequential_order = '005'
left join app_sample sCol006 on sCol006.id_analysis = a.id and sCol006.sequential_order = '006'
left join app_sample sCol007 on sCol007.id_analysis = a.id and sCol007.sequential_order = '007'
left join app_sample sCol008 on sCol008.id_analysis = a.id and sCol008.sequential_order = '008'

And surely the query will be faster.

Abs.

  • 1

    Cool the suggestion @Rodolpho Sa, It would decrease the lines well, however I still have the problem of sequential is not exactly a sequence and there may be failures. Thanks for the tip.

  • Maybe you can build a precedent that generates a dynamic query string (using this example as a template ) and then run it.

0

Jeferson Cruz the best way to sort results is with order by and then the field you want to sort, eg: select * from app_sample order by sequential_order.

You can take the example of Rodolpho Sa and add the order by.

I hope I helped, hug.

Citation

Browser other questions tagged sql postgresql report

You are not signed in. Login or sign up in order to post.