How to split a single row into multiple rows in SQL

sql, sql-function, sql-server, sql-server-2012, sqlperformance

Solution

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

Problem

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... Is there a better n faster way to do this? May be using a custom function?

Original source