How to split a single row into multiple rows in SQL

Question

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?

the table is not indexed and i'm not the admin for this SQL server so i can't add indexes. Any other technique to speed up the query ? since CROSS APPLY performance is same as UNION ALL as mentioned below — user330612, Jul 26 '13 at 21:09

Hart CO · Accepted Answer · 2013-07-26T17:56:00.040

7

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

edited Jul 26 '13 at 17:56

answered Jul 26 '13 at 17:50

Hart CO

34,064
6
48
63

Thanks for the replies. Is using PIVOT be more performant than CROSS APPLY? http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx – user330612 Jul 26 '13 at 18:49
I'm not sure how you would go about it, or if the performance would be any different. – Hart CO Jul 26 '13 at 19:37
I just modified my initial query which was using UNION ALLs to use CROSS APPLY but I don't see any difference in performance. Both took almost 47 seconds on avg. So using CROSS apply isn't giving me any advantage over using UNIONALL multiple times! any other idea? My table contains 253730 rows and 47 columns and final output table contains 3552220 rows. – user330612 Jul 26 '13 at 20:01
That makes sense, it's simplifying the query but it's doing fundamentally the same thing. – Hart CO Jul 26 '13 at 20:11

How to split a single row into multiple rows in SQL

1 Answers1