SQL Pivot Column to Rows

Question

[SQL Pivot from columns to Rows]

current data structure

Hello Folks, I'm trying to convert the columns in a table to Row for a requirment, I was able to bring multiple column values to one column, but dont know how to transpose to rows. Appreciate your help

WITH mn_result AS(
SELECT 
eh.[RUN_NUMBER], ed.[NOTCH_STEP], ed.[POINT_TYPE], ed.[TESTDATA_ID]
FROM [dbo].[Header] eh 
JOIN
[dbo].[TestData] ed ON eh.[RUN_NUMBER] = ed.[RUN_NUMBER] 

WHERE eh.[RUN_NUMBER] = '01007230')

SELECT [RUN_NUMBER]
,col+value as col,value FROM (
SELECT  [RUN_NUMBER],[NOTCH_STEP] as notch,[POINT_TYPE] as ptype
,[TESTDATA_ID] as id FROM mn_result 
GROUP BY [RUN_NUMBER],[NOTCH_STEP],[POINT_TYPE],[TESTDATA_ID]) rt
unpivot ( value FOR col in (notch,ptype))unpiv
ORDER BY col

Answer 1

Consider PIVOT with CROSS JOIN or CROSS APPLY where both SELECT statements pivot off the ENGINETESTDATA_ID column:

WITH mn_result AS (
   SELECT eh.ENGINETESTDATA_ID, eh.[RUN_NUMBER], ed.[NOTCH_STEP], 
          ed.[POINT_TYPE], ed.[TESTDATA_ID]
   FROM [dbo].[Header] eh 
   JOIN [dbo].[TestData] ed ON eh.[RUN_NUMBER] = ed.[RUN_NUMBER] 
   WHERE eh.[RUN_NUMBER] = '01007230'
)

SELECT s1.*, s2.*
FROM
(
SELECT t.[118427] As [Notch1], 
       t.[118428] As [Notch2], 
       t.[118429] As [Notch3], 
       t.[118430] As [Notch4], 
       t.[118431] As [Notch5], 
       t.[118432] As [Notch6], 
       t.[118433] As [Notch7]

FROM (SELECT ENGINETESTDATA_ID, NOTCH_STEP FROM mn_result) r

PIVOT 
  (
    MAX(r.[NOTCH_STEP]) 
    FOR r.ENGINETESTDATA_ID IN ([118427], [118428], [118429], [118430], 
                                [118431], [118432], [118432])
  ) AS t
) s1

CROSS APPLY
(
SELECT t.[118427] As [Ptype1], 
       t.[118428] As [Ptype2], 
       t.[118429] As [Ptype3], 
       t.[118430] As [Ptype4], 
       t.[118431] As [Ptype5], 
       t.[118432] As [Ptype6], 
       t.[118433] As [Ptype7]

FROM (SELECT ENGINETESTDATA_ID, POINT_TYPE FROM mn_result) r

PIVOT 
  (
    MAX(r.[POINT_TYPE]) 
    FOR r.ENGINETESTDATA_ID IN ([118427], [118428], [118429], [118430], 
                                [118431], [118432], [118433])
  ) AS t
) s2

Rextester Demo