How to Split Data in TSQL

Question

need some help figuring out how to split up some data

the Data Currently looks like this

╔═════════════════════════════════════════════╗
║ Name Data1 Data2 Data3 Field1 field2 Field3 ║
╠═════════════════════════════════════════════╣
║ a     1      2     3     x      y      z    ║
╚═════════════════════════════════════════════╝

I need to split that data so that it looks like this

+-----------------+
| name data field |
+-----------------+
| a     1     x   |
| a     2     y   |
| a     3     z   |
+-----------------+

can anyone help me with this

Answer 1

Depending on your version of SQL Server. Starting in SQL Server 2008, you can unpivot the data using CROSS APPLY:

SELECT t.name,
  x.Data,
  x.Field
FROM YourTable t
CROSS APPLY 
(
    VALUES
        (t.Data1, t.Field1),
        (t.Data2, t.Field2),
        (t.Data3, t.Field3)
) x (Data, Field);

See SQL Fiddle with Demo.

This can also be done using the UNPIVOT and the PIVOT function in SQL Server 2005+:

select name, data, field
from
(
  select name, left(col, len(col) -1) col, value,
    row_number() over(partition by left(col, len(col) -1) order by col) rn
  from
  (
    select name, 
      cast([Data1] as varchar(10)) Data1, 
      cast([Data2] as varchar(10)) Data2, 
      cast([Data3] as varchar(10)) Data3, 
      [Field1], [field2], [Field3]
    from yourtable
  ) src
  unpivot
  (
    value
    for col in ([Data1], [Data2], [Data3], 
                [Field1], [field2], [Field3])
  ) unpiv
) u
pivot
(
  max(value)
  for col in (data,field)
) piv

See SQL Fiddle with Demo

Both give the result:

| NAME | DATA | FIELD |
-----------------------
|    a |    1 |     x |
|    a |    2 |     y |
|    a |    3 |     z |