如何在SQL中将一行分割为多行

I have a table which looks as follows..it has multiple columns for different latencies

我有一张桌子，它的形状如下对于不同的延迟，它有多个列

Date   API    Latency1_Avg Latency1_Min Latency1_Max Latency2_Avg Latency2_Min Latency2_Max
----   ---    ------------ ------------ ------------ ------------ ------------ ------------
7/26/13  Foo    12              35          45           453           433         435
7/26/13  Bar    33              33          33           234           243         234

I want output a table which splits each row into multiple rows as follows

我希望输出一个表，该表将每行分割为如下所示的多行

Date    API   Latency  Aggregation  Value
----    ----  -------  -----------   -----
7/26/13 Foo   Latency1   Avg         12
7/26/13 Foo   Latency1   Min         35
7/26/13 Foo   Latency1   Max         45
7/26/13 Foo   Latency2   Avg         453
7/26/13 Foo   Latency2   Min         433
7/26/13 Foo   Latency2   Max         435

.....

Right now, What I'm doing is this

现在，我要做的就是这个。

    SELECT 
    Date,
    API, 
   "Latency1" AS Latency,
    "Avg" AS Calculation,
    Latency1_Avg AS Value  
    FROM Table UNION ALL
    SELECT 
    Date,
    API, 
    "Latency1" AS Latency,
    "Min" AS Calculation,
    Latency1_Min AS Value  
    FROM Table UNION ALL
    SELECT 
    Date,
    API, 
    "Latency1" AS Latency,
    "Max" AS Calculation,
    Latency1_Max AS Value  
    FROM Table UNION ALL

.... so on

....等等

This is very inefficient performance wise, because i'm doing a table scan for each of the select statement...when this table is huge then it takes a long time to query...

这是非常低效的性能，因为我正在对每个select语句进行表扫描……当这个表很大时，它需要很长时间来查询……

Is there a better n faster way to do this? May be using a custom function?

有更好的更快的方法吗?可能使用自定义函数?

1 个解决方案

#1

You can use CROSS APPLY:

你可以使用交叉申请:

SELECT [Date]
      , API
      , LEFT(col,CHARINDEX('_',col)-1)'Latency'
      , RIGHT(col,CHARINDEX('_',REVERSE(col))-1)'Aggregation'
      , Value
FROM
(
  SELECT [Date],API,col,value
  FROM YourTable
  CROSS APPLY
  (
    VALUES ('Latency1_Avg', [Latency1_Avg]),('Latency1_Min', [Latency1_Min]),('Latency1_Max', [Latency1_Max]),('Latency2_Avg', [Latency2_Avg]),('Latency2_Min', [Latency2_Min]),('Latency2_Max', [Latency2_Max])
  ) C (COL, VALUE)
) SRC

Demo: SQL Fiddle

演示:SQL小提琴

#1