SQL Pivot Column to Rows

Question

[SQL Pivot from columns to Rows]

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

What RDBMS is this for? Please add a relevant tag to clear up — marc_s, Jan 18 '18 at 18:24
Desired output is not valid as you have repeating headers with 8's. — Parfait, Jan 18 '18 at 19:09

Parfait · Accepted Answer · 2018-01-19T16:34:56.287

0

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

edited Jan 19 '18 at 16:34

answered Jan 18 '18 at 19:41

Parfait

104,375
17
94
125

@Parfait- thank you for your help. The Pivot generate values as "NULL", but the mn_result - select did generate values. – Mnat Jan 18 '18 at 20:23
Nope.. added another image of current data structure.. please see if you could help me if possible – Mnat Jan 18 '18 at 22:09
See edit and also look at the demo link with example data. Try tailoring your actual data to align with example. Notice how the pivot column, now `ENGINETESTDATA_ID` is pivoted on its values `[118427], [118428],...` as new headers and the row values are the aggregated columns, `MAX(r.[NOTCH_STEP])` and `MAX(r.[POINT_TYPE])`. – Parfait Jan 19 '18 at 15:08
You are Awesome!!. pls suggest me how to run this program for dynamic Run numbers, coz the Enginetestdata_ID were specific to run number '01007230' if I change , then it would fail. – Mnat Jan 19 '18 at 15:19
Oh no.. the earlier version works perfect for a value '01007230', and for this run number the ID's are '118427,28,29...', but if i replace the run number to '01007230' the Id's could be '118477,78,79..',. how to change this in the pivot to have dynamic Id's. hope you could through some suggestions – Mnat Jan 19 '18 at 15:57
Got it. I rolled back changes. The dynamic pivot is not an easy endeavor as you have to build the `SELECT` statement in code. See [here](https://stackoverflow.com/q/10404348/1422451). I recommend general-purpose language like R and Python pandas which can easily reshape data and can connect to SQL Server! SQL is a limited, special-purpose language. – Parfait Jan 19 '18 at 16:37

SQL Pivot Column to Rows

1 Answers1