Sort query result in Ordered Row

Navigation
6

I have the following query that I get with this query:

Select Año, Tip, Est, t1, t2, t3 From Tabla1 

Año     Tip Est Zo  t1  t2  t3  

2003    1   A   6   12  14  17  
2003    1   A   7   12  15  18  
2003    1   A   8   13  16  19  
2003    1   A   9   10  12  14  
2003    1   A   10  12  14  17  
2004    1   A   1   11  13  15

How can I perform the query to have a result similar to this? 

Año     Tip Est Zo  Ts 

2003    1   A   6   12
2003    1   A   6   14
2003    1   A   6   17
2003    1   A   7   12
2003    1   A   7   15
2003    1   A   7   18
2003    1   A   8   13
2003    1   A   8   16
2003    1   A   8   19
2003    1   A   9   10
2003    1   A   9   12
2003    1   A   9   14

What you are doing is placing the columns t1, t2, t3 in a row with the same years they belong to.

How could I achieve that?

    
asked by Hans 21.10.2016 в 23:08
source

3 answers

6

SQL Server allows you to do a UNPIVOT , designed just for this situation: 

select año, tip, est, zo, ts
  from tabla1
unpivot (
  ts for columnas in (t1, t2, t3)
) as unpvt
 order by ... -- aquí le pones las columnas que necesitas para establecer el orden deseado

Another interesting option is to use CROSS APPLY : 

select t.año, t.tip, t.est, t.zo, v.ts
  from tabla1 t
 cross apply (
   values (t.t1), (t.t2), (t.t3)
 ) as v(ts)
 order by ... -- aquí le pones las columnas que necesitas para establecer el orden deseado

In both cases, the performance should be good since you only need to go through the table once.

    
answered by 22.10.2016 / 00:02
source
3

While I think I understand you want to group it in a single column, you can do it with a union all 

SELECT  *
FROM
    ((SELECT 
        Año, Tip, Est, Zo, t1
    FROM
        Tabla1) 
UNION ALL (SELECT 
        Año, Tip, Est, Zo, t2
    FROM
        Tabla1) 
UNION ALL (SELECT 
        Año, Tip, Est, Zo, t3
    FROM
        Tabla1)) tabla
ORDER BY Est;
    
answered by 21.10.2016 в 23:16
-2

You must add ORDER BY .

Try the following query: 

SELECT Año, Tip, Est, t1
FROM Tabla1
ORDER BY Zo
    
answered by 21.10.2016 в 23:11
What is the difference between declaring a JSON object with: y =? For what is "=" in C #