SQL Server row to columns

Question

I have data in a SQL Table with the following structure:

Engine_No Date  Process Bolt_No1 B1Value1 B1Value2 B1Value3 Bolt_No2 B2Value1 B2Value2 B2Value3  Bolt_No3 B3Value1 B3Value2 B3Value3 Bolt_No4 B4Value1 B4Value2 B4Value3

I want to display the following results of a query:

SR NO  Engine_No Date  Process B1Bolt_No1 B1Value1 B1Value2 B1Value3          
SR NO  Engine_No Date  Process B2Bolt_No2 B2Value1 B2Value2 B2Value3   
SR NO  Engine_No Date  Process B3Bolt_No3 B3Value1 B3Value2 B3Value3  
SR NO  Engine_No Date  Process B4Bolt_No4 B4Value1 B4Value2 B4Value3

Can someone suggest a Query that achieves this?

Answer 1

Assuming the values are actually stored in valid column names, then the simplest way is to use union all. However, a more performing way is unpivot or cross apply:

select t.Engine_No, t.Date, t.Process, b.Bolt_No, b.Value1, b.Value2, b.Value3 
from t cross apply
     (values (Bolt_No1, Value1_1 Value1_2 Value1_3),
             (Bolt_No2, Value2_1 Value2_2 Value2_3),
             (Bolt_No3, Value3_1 Value3_2 Value3_3),
             (Bolt_No4, Value4_1 Value4_2 Value4_3)
     ) b(Bolt_No, Value1 Value2 Value3);

I have no idea what SR NO is. It is not mentioned in the data.