Rows in Columns (SQL)

Question

Rows in Columns (SQL)

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

I've been thinking of a few days ago to make a select less verbose, turning rows into columns. Today I have it this 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 is in rows and need to turn columns like this and are not just 8 columns - can vary up to 100 for each item according to its sequence. Second that the order received is not always sequential, for example ('001', '003', '040'), because it depends on the situation.

The script in this format is giant and impractical to maintain, another is that I need to sort the sequential so there is no gap in the report display.

I am open to suggestions and discussions.

Thank you.

sql postgresql

asked by anonymous 01.06.2017 / 18:55

2 answers

1

Jeferson Cruz the best way to sort results is with the 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 to have helped, hug.

Quote

05.06.2017 / 03:54

Controller can have private methods? Left Join in LINQ

score 1 · Accepted Answer

One option would look 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 of course the query will be faster.

Abs.