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?
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