CODEWITHSUNDEEP
sqlsql-serversql-server-2012pivot
user3055889
user3055889

Reputation: 95

SQL Server 2012 Breaking Data Up From One Column Into Multiple

I have three very long columns of data shown below . enter image description here

The Data gets added into the database in 214 line block, each line is the value of a different variable at that time stamp. The time stamp will be the same for each of the 214 lines per block of data. I'm trying to take the blocks and break them up into individual columns/rows (if possible I would like to make this dynamic vs entering each name of a column or row).

Either solution below would work.

enter image description here

enter image description here

I've looked into using PIVOT but it seems like that requires arithmetically combining data not just breaking it out into separate columns/rows.

This will be used to generate an SQL report that may be looked at once a month or so, so performance is not that big of an issue.

Dynamic pivot generated by @Rigerta Demiri code below

    select TagTimestamp,  AA_Mode, CDR01_Torque_Max, CDR02_Torque_Max, CDR03_Torque_Max, CDR04_Torque_Max, CDR05_Torque_Max, CDR06_Index_Position_0, CDR06_Index_Position_1, CDR06_Torque_Max, CDR07_Index_Position_0, CDR07_Index_Position_1, CDR07_Torque_Max, CDR09_Axis_Offset, CDR09_Torque_Max, CDR10_Torque_Max, CDR11_Torque_Max, CDR12_Torque_Max, CDR13_Torque_Max, CDR14_Index_Position_3, CDR14_Index_Position_4, CDR14_Torque_Max, CDR17_Axis_Offset, CDR17_Slave_Scaling, CDR17_Torque_Max, CDR18_Axis_Offset, CDR18_Slave_Scaling, CDR18_Torque_Max, CDR19_Axis_Offset, CDR19_Slave_Scaling, CDR19_Torque_Max, CDR20_Axis_Offset, CDR20_Slave_Scaling, CDR20_Torque_Max, CDR21_Axis_Offset, CDR21_Slave_Scaling, CDR21_Torque_Max, CDR22_Axis_Offset, CDR22_Slave_Scaling, CDR22_Torque_Max, CDR25_Axis_Offset, CDR25_Slave_Scaling, CDR25_Torque_Max, CDR26_Axis_Offset, CDR26_Slave_Scaling, CDR26_Torque_Max, CDR27_Axis_Offset, CDR27_Slave_Scaling, CDR27_Torque_Max, CDR28_Axis_Offset, CDR28_Slave_Scaling, CDR28_Torque_Max, CDR29_Axis_Offset, CDR29_Slave_Scaling, CDR29_Torque_Max, CDR30_Axis_Offset, CDR30_Slave_Scaling, CDR30_Torque_Max, CDR33_Index_Position_1, CDR33_Index_Position_2, CDR33_Torque_Max, CDR34_Torque_Max, CDR35_Torque_Max, CDR36_Slave_Scaling, CDR36_Torque_Max, CDR37_Slave_Scaling, CDR37_Torque_Max, CDR38_Slave_Scaling, CDR38_Torque_Max, CVFD42_Speed_Comp, CVFD43_Speed_Comp, CVFD44_Speed_Comp, CVFD45_Speed_Comp, CVFD46_Speed_Comp, CVFD47_Speed_Comp, CVFD48_Speed_Comp, Carton_Vac_Pick_Max_Mispick_Limit, Changeover_Points[0], Changeover_Points[100], Changeover_Points[101], Changeover_Points[10], Changeover_Points[11], Changeover_Points[12], Changeover_Points[13], Changeover_Points[14], Changeover_Points[15], Changeover_Points[16], Changeover_Points[17], Changeover_Points[18], Changeover_Points[19], Changeover_Points[1], Changeover_Points[20], Changeover_Points[21], Changeover_Points[22], Changeover_Points[23], Changeover_Points[24], Changeover_Points[25], Changeover_Points[26], Changeover_Points[27], Changeover_Points[28], Changeover_Points[29], Changeover_Points[2], Changeover_Points[30], Changeover_Points[31], Changeover_Points[32], Changeover_Points[33], Changeover_Points[34], Changeover_Points[35], Changeover_Points[36], Changeover_Points[37], Changeover_Points[38], Changeover_Points[39], Changeover_Points[3], Changeover_Points[40], Changeover_Points[41], Changeover_Points[42], Changeover_Points[43], Changeover_Points[44], Changeover_Points[45], Changeover_Points[46], Changeover_Points[47], Changeover_Points[48], Changeover_Points[49], Changeover_Points[4], Changeover_Points[50], Changeover_Points[51], Changeover_Points[52], Changeover_Points[53], Changeover_Points[54], Changeover_Points[55], Changeover_Points[56], Changeover_Points[57], Changeover_Points[58], Changeover_Points[59], Changeover_Points[5], Changeover_Points[60], Changeover_Points[61], Changeover_Points[62], Changeover_Points[63], Changeover_Points[64], Changeover_Points[65], Changeover_Points[66], Changeover_Points[67], Changeover_Points[68], Changeover_Points[69], Changeover_Points[6], Changeover_Points[70], Changeover_Points[71], Changeover_Points[72], Changeover_Points[73], Changeover_Points[74], Changeover_Points[75], Changeover_Points[76], Changeover_Points[77], Changeover_Points[78], Changeover_Points[79], Changeover_Points[7], Changeover_Points[80], Changeover_Points[81], Changeover_Points[82], Changeover_Points[83], Changeover_Points[84], Changeover_Points[85], Changeover_Points[86], Changeover_Points[87], Changeover_Points[88], Changeover_Points[89], Changeover_Points[8], Changeover_Points[90], Changeover_Points[91], Changeover_Points[92], Changeover_Points[93], Changeover_Points[94], Changeover_Points[95], Changeover_Points[96], Changeover_Points[97], Changeover_Points[98], Changeover_Points[99], Changeover_Points[9], Delta_1_Effector_Cycle_Jam_Delay, Delta_2_Effector_Cycle_Jam_Delay, Delta_2_Loading_Index_4_XZ_0, Delta_2_Loading_Index_4_XZ_1, Delta_2_Loading_Index_6_XZ_0, Delta_2_Loading_Index_6_XZ_1, Delta_2_Loading_Index_7_XZ_0, Delta_2_Loading_Index_7_XZ_1, Delta_3_Effector_Cycle_Jam_Delay, Delta_4_Effector_Cycle_Jam_Delay, Delta_4_Loading_Index_4_XZ_0, Delta_4_Loading_Index_4_XZ_1, Delta_4_Loading_Index_6_XZ_0, Delta_4_Loading_Index_6_XZ_1, Delta_4_Loading_Index_7_XZ_0, Delta_4_Loading_Index_7_XZ_1, Delta_5_Effector_Cycle_Jam_Delay, Delta_6_Effector_Cycle_Jam_Delay, Discharge_Carton_Blocked_Delay_On, Discharge_Carton_Dischrage_Jam_Delay, Glue_Low_Level_Supply_Limit, Laner_Conveyor_Product_Count1, Laner_Conveyor_Product_Count2, Laner_Conveyor_Side_Belts_Off_Delay, Machine_Speed, Magazine_Advance_Delay, Magazine_Advance_Fault_Delay, Magzine_Low_Level_ShutDown_Limit, Major_FLap_Inspection_Cons_Fail_Limit, Master_Data_Code, Minor_Flap_Inspection_Cons_Fail_Limit, Outer_Carton_Mode, Recipe_Name, Recipe_Number, ThirtySix_Count_Mode, Vision_Job_Data
              from 
                (
                    select TagTimestamp, TagItemID, TagValue
                    from #allData2
                ) x
              pivot ( max(TagValue) for TagItemID in ( AA_Mode, CDR01_Torque_Max, CDR02_Torque_Max, CDR03_Torque_Max, CDR04_Torque_Max, CDR05_Torque_Max, CDR06_Index_Position_0, CDR06_Index_Position_1, CDR06_Torque_Max, CDR07_Index_Position_0, CDR07_Index_Position_1, CDR07_Torque_Max, CDR09_Axis_Offset, CDR09_Torque_Max, CDR10_Torque_Max, CDR11_Torque_Max, CDR12_Torque_Max, CDR13_Torque_Max, CDR14_Index_Position_3, CDR14_Index_Position_4, CDR14_Torque_Max, CDR17_Axis_Offset, CDR17_Slave_Scaling, CDR17_Torque_Max, CDR18_Axis_Offset, CDR18_Slave_Scaling, CDR18_Torque_Max, CDR19_Axis_Offset, CDR19_Slave_Scaling, CDR19_Torque_Max, CDR20_Axis_Offset, CDR20_Slave_Scaling, CDR20_Torque_Max, CDR21_Axis_Offset, CDR21_Slave_Scaling, CDR21_Torque_Max, CDR22_Axis_Offset, CDR22_Slave_Scaling, CDR22_Torque_Max, CDR25_Axis_Offset, CDR25_Slave_Scaling, CDR25_Torque_Max, CDR26_Axis_Offset, CDR26_Slave_Scaling, CDR26_Torque_Max, CDR27_Axis_Offset, CDR27_Slave_Scaling, CDR27_Torque_Max, CDR28_Axis_Offset, CDR28_Slave_Scaling, CDR28_Torque_Max, CDR29_Axis_Offset, CDR29_Slave_Scaling, CDR29_Torque_Max, CDR30_Axis_Offset, CDR30_Slave_Scaling, CDR30_Torque_Max, CDR33_Index_Position_1, CDR33_Index_Position_2, CDR33_Torque_Max, CDR34_Torque_Max, CDR35_Torque_Max, CDR36_Slave_Scaling, CDR36_Torque_Max, CDR37_Slave_Scaling, CDR37_Torque_Max, CDR38_Slave_Scaling, CDR38_Torque_Max, CVFD42_Speed_Comp, CVFD43_Speed_Comp, CVFD44_Speed_Comp, CVFD45_Speed_Comp, CVFD46_Speed_Comp, CVFD47_Speed_Comp, CVFD48_Speed_Comp, Carton_Vac_Pick_Max_Mispick_Limit, Changeover_Points[0], Changeover_Points[100], Changeover_Points[101], Changeover_Points[10], Changeover_Points[11], Changeover_Points[12], Changeover_Points[13], Changeover_Points[14], Changeover_Points[15], Changeover_Points[16], Changeover_Points[17], Changeover_Points[18], Changeover_Points[19], Changeover_Points[1], Changeover_Points[20], Changeover_Points[21], Changeover_Points[22], Changeover_Points[23], Changeover_Points[24], Changeover_Points[25], Changeover_Points[26], Changeover_Points[27], Changeover_Points[28], Changeover_Points[29], Changeover_Points[2], Changeover_Points[30], Changeover_Points[31], Changeover_Points[32], Changeover_Points[33], Changeover_Points[34], Changeover_Points[35], Changeover_Points[36], Changeover_Points[37], Changeover_Points[38], Changeover_Points[39], Changeover_Points[3], Changeover_Points[40], Changeover_Points[41], Changeover_Points[42], Changeover_Points[43], Changeover_Points[44], Changeover_Points[45], Changeover_Points[46], Changeover_Points[47], Changeover_Points[48], Changeover_Points[49], Changeover_Points[4], Changeover_Points[50], Changeover_Points[51], Changeover_Points[52], Changeover_Points[53], Changeover_Points[54], Changeover_Points[55], Changeover_Points[56], Changeover_Points[57], Changeover_Points[58], Changeover_Points[59], Changeover_Points[5], Changeover_Points[60], Changeover_Points[61], Changeover_Points[62], Changeover_Points[63], Changeover_Points[64], Changeover_Points[65], Changeover_Points[66], Changeover_Points[67], Changeover_Points[68], Changeover_Points[69], Changeover_Points[6], Changeover_Points[70], Changeover_Points[71], Changeover_Points[72], Changeover_Points[73], Changeover_Points[74], Changeover_Points[75], Changeover_Points[76], Changeover_Points[77], Changeover_Points[78], Changeover_Points[79], Changeover_Points[7], Changeover_Points[80], Changeover_Points[81], Changeover_Points[82], Changeover_Points[83], Changeover_Points[84], Changeover_Points[85], Changeover_Points[86], Changeover_Points[87], Changeover_Points[88], Changeover_Points[89], Changeover_Points[8], Changeover_Points[90], Changeover_Points[91], Changeover_Points[92], Changeover_Points[93], Changeover_Points[94], Changeover_Points[95], Changeover_Points[96], Changeover_Points[97], Changeover_Points[98], Changeover_Points[99], Changeover_Points[9], Delta_1_Effector_Cycle_Jam_Delay, Delta_2_Effector_Cycle_Jam_Delay, Delta_2_Loading_Index_4_XZ_0, Delta_2_Loading_Index_4_XZ_1, Delta_2_Loading_Index_6_XZ_0, Delta_2_Loading_Index_6_XZ_1, Delta_2_Loading_Index_7_XZ_0, Delta_2_Loading_Index_7_XZ_1, Delta_3_Effector_Cycle_Jam_Delay, Delta_4_Effector_Cycle_Jam_Delay, Delta_4_Loading_Index_4_XZ_0, Delta_4_Loading_Index_4_XZ_1, Delta_4_Loading_Index_6_XZ_0, Delta_4_Loading_Index_6_XZ_1, Delta_4_Loading_Index_7_XZ_0, Delta_4_Loading_Index_7_XZ_1, Delta_5_Effector_Cycle_Jam_Delay, Delta_6_Effector_Cycle_Jam_Delay, Discharge_Carton_Blocked_Delay_On, Discharge_Carton_Dischrage_Jam_Delay, Glue_Low_Level_Supply_Limit, Laner_Conveyor_Product_Count1, Laner_Conveyor_Product_Count2, Laner_Conveyor_Side_Belts_Off_Delay, Machine_Speed, Magazine_Advance_Delay, Magazine_Advance_Fault_Delay, Magzine_Low_Level_ShutDown_Limit, Major_FLap_Inspection_Cons_Fail_Limit, Master_Data_Code, Minor_Flap_Inspection_Cons_Fail_Limit, Outer_Carton_Mode, Recipe_Name, Recipe_Number, ThirtySix_Count_Mode, Vision_Job_Data) )p  
          order by TagTimestamp

Problem with the above code was that I had special characters '[' and ']' in some of the tag names which caused sql to error out. Removing those characters caused the code to work correctly.

Upvotes: 0

Views: 69

Answers (1)

Rigerta
Rigerta

Reputation: 4039

A dynamic pivot would do the work, in case you don't already know all possible TagItemIDs. If you do, a simple pivot would also work.

Example code: 

declare @cols_tagItems as nvarchar(max)
     ,  @cols_tagItems_max as nvarchar(max)
     ,  @query  as nvarchar(max)

select @cols_tagItems = stuff((select distinct ', ' + TagItemID from #table for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'')
 select @cols_tagItems

set @query = 'select TagTimestamp, ' + @cols_tagItems + '
                  from 
                    (
                        select TagTimestamp, TagItemID, TagValue
                        from #table 
                    ) x
                  pivot ( max(TagValue) for TagItemID in (' + @cols_tagItems + ') )p  
              order by TagTimestamp'
select @query
execute sp_executesql @query;

You can check out a working demo here. Please note I only included three TagItemIds and just assigned some TagValues to them.

Upvotes: 4

Related Questions