Transposing SQL Record
-
I have a sql table with following schema. DateStamp BatchID MixName MixComment BatchDestination BatchComment MixNo MixRev Ing1Name Ing1Size Ing1PRM Ing1AcceptedUnderTol Ing1SPCSample Ing1TargetWgt Ing1ActualWgt Ing1PartialActualWgt Ing1PreactWgt Ing1SlowFeedTime Ing1FastFeedTime Ing2Name Ing2Size Ing2PRM Ing2AcceptedUnderTol Ing2SPCSample Ing2TargetWgt Ing2ActualWgt Ing2PartialActualWgt Ing2PreactWgt Ing2SlowFeedTime Ing2FastFeedTime Ing3Name Ing3Size Ing3PRM Ing3AcceptedUnderTol Ing3SPCSample Ing3TargetWgt Ing3ActualWgt Ing3PartialActualWgt Ing3PreactWgt Ing3SlowFeedTime Ing3FastFeedTime Ing4Name Ing4Size Ing4PRM Ing4AcceptedUnderTol Ing4SPCSample Ing4TargetWgt Ing4ActualWgt Ing4PartialActualWgt Ing4PreactWgt Ing4SlowFeedTime Ing4FastFeedTime Ing5Name Ing5Size Ing5PRM Ing5AcceptedUnderTol Ing5SPCSample Ing5TargetWgt Ing5ActualWgt Ing5PartialActualWgt Ing5PreactWgt Ing5SlowFeedTime Ing5FastFeedTime Ing6Name Ing6Size Ing6PRM Ing6AcceptedUnderTol Ing6SPCSample Ing6TargetWgt Ing6ActualWgt Ing6PartialActualWgt Ing6PreactWgt Ing6SlowFeedTime Ing6FastFeedTime Ing7Name Ing7Size Ing7PRM Ing7AcceptedUnderTol Ing7SPCSample Ing7TargetWgt Ing7ActualWgt Ing7PartialActualWgt Ing7PreactWgt Ing7SlowFeedTime Ing7FastFeedTime Ing8Name Ing8Size Ing8PRM Ing8AcceptedUnderTol Ing8SPCSample Ing8TargetWgt Ing8ActualWgt Ing8PartialActualWgt Ing8PreactWgt Ing8SlowFeedTime Ing8FastFeedTime Ing9Name Ing9Size Ing9PRM Ing9AcceptedUnderTol Ing9SPCSample Ing9TargetWgt Ing9ActualWgt Ing9PartialActualWgt Ing9PreactWgt Ing9SlowFeedTime Ing9FastFeedTime Ing10Name Ing10Size Ing10PRM Ing10AcceptedUnderTol Ing10SPCSample Ing10TargetWgt Ing10ActualWgt Ing10PartialActualWgt Ing10PreactWgt Ing10SlowFeedTime Ing10FastFeedTime Ing11Name Ing11Size Ing11PRM Ing11AcceptedUnderTol Ing11SPCSample Ing11TargetWgt Ing11ActualWgt Ing11PartialActualWgt Ing11PreactWgt Ing11SlowFeedTime Ing11FastFeedTime Ing12Name Ing12Size Ing12PRM Ing12AcceptedUnderTol Ing12SPCSample Ing12TargetWgt Ing12ActualWgt Ing12PartialActualWgt Ing12PreactWgt Ing12SlowFeedTime Ing12FastFeedTime TimeStarted TimeComp ManIntervention HA_Ing1Name HA_Ing1Size HA_Ing1PRM HA_Ing1TargetWgt HA_Ing1ActualWgt HA_Ing2Name HA_Ing2Size HA_Ing2PRM HA_Ing2TargetWgt HA_Ing2ActualWgt HA_Ing3Name HA_Ing3Size HA_Ing3PRM HA_Ing3TargetWgt HA_Ing3ActualWgt HA_Ing4Name HA_Ing4Size HA_Ing4PRM HA_Ing4TargetWgt HA_Ing4ActualWgt HA_Ing5Name HA_Ing5Size HA_I
-
I have a sql table with following schema. DateStamp BatchID MixName MixComment BatchDestination BatchComment MixNo MixRev Ing1Name Ing1Size Ing1PRM Ing1AcceptedUnderTol Ing1SPCSample Ing1TargetWgt Ing1ActualWgt Ing1PartialActualWgt Ing1PreactWgt Ing1SlowFeedTime Ing1FastFeedTime Ing2Name Ing2Size Ing2PRM Ing2AcceptedUnderTol Ing2SPCSample Ing2TargetWgt Ing2ActualWgt Ing2PartialActualWgt Ing2PreactWgt Ing2SlowFeedTime Ing2FastFeedTime Ing3Name Ing3Size Ing3PRM Ing3AcceptedUnderTol Ing3SPCSample Ing3TargetWgt Ing3ActualWgt Ing3PartialActualWgt Ing3PreactWgt Ing3SlowFeedTime Ing3FastFeedTime Ing4Name Ing4Size Ing4PRM Ing4AcceptedUnderTol Ing4SPCSample Ing4TargetWgt Ing4ActualWgt Ing4PartialActualWgt Ing4PreactWgt Ing4SlowFeedTime Ing4FastFeedTime Ing5Name Ing5Size Ing5PRM Ing5AcceptedUnderTol Ing5SPCSample Ing5TargetWgt Ing5ActualWgt Ing5PartialActualWgt Ing5PreactWgt Ing5SlowFeedTime Ing5FastFeedTime Ing6Name Ing6Size Ing6PRM Ing6AcceptedUnderTol Ing6SPCSample Ing6TargetWgt Ing6ActualWgt Ing6PartialActualWgt Ing6PreactWgt Ing6SlowFeedTime Ing6FastFeedTime Ing7Name Ing7Size Ing7PRM Ing7AcceptedUnderTol Ing7SPCSample Ing7TargetWgt Ing7ActualWgt Ing7PartialActualWgt Ing7PreactWgt Ing7SlowFeedTime Ing7FastFeedTime Ing8Name Ing8Size Ing8PRM Ing8AcceptedUnderTol Ing8SPCSample Ing8TargetWgt Ing8ActualWgt Ing8PartialActualWgt Ing8PreactWgt Ing8SlowFeedTime Ing8FastFeedTime Ing9Name Ing9Size Ing9PRM Ing9AcceptedUnderTol Ing9SPCSample Ing9TargetWgt Ing9ActualWgt Ing9PartialActualWgt Ing9PreactWgt Ing9SlowFeedTime Ing9FastFeedTime Ing10Name Ing10Size Ing10PRM Ing10AcceptedUnderTol Ing10SPCSample Ing10TargetWgt Ing10ActualWgt Ing10PartialActualWgt Ing10PreactWgt Ing10SlowFeedTime Ing10FastFeedTime Ing11Name Ing11Size Ing11PRM Ing11AcceptedUnderTol Ing11SPCSample Ing11TargetWgt Ing11ActualWgt Ing11PartialActualWgt Ing11PreactWgt Ing11SlowFeedTime Ing11FastFeedTime Ing12Name Ing12Size Ing12PRM Ing12AcceptedUnderTol Ing12SPCSample Ing12TargetWgt Ing12ActualWgt Ing12PartialActualWgt Ing12PreactWgt Ing12SlowFeedTime Ing12FastFeedTime TimeStarted TimeComp ManIntervention HA_Ing1Name HA_Ing1Size HA_Ing1PRM HA_Ing1TargetWgt HA_Ing1ActualWgt HA_Ing2Name HA_Ing2Size HA_Ing2PRM HA_Ing2TargetWgt HA_Ing2ActualWgt HA_Ing3Name HA_Ing3Size HA_Ing3PRM HA_Ing3TargetWgt HA_Ing3ActualWgt HA_Ing4Name HA_Ing4Size HA_Ing4PRM HA_Ing4TargetWgt HA_Ing4ActualWgt HA_Ing5Name HA_Ing5Size HA_I
You may want to create a view which satisfies the requirement rather than a whole new table. Just a thought. :rose: Good luck
-
You may want to create a view which satisfies the requirement rather than a whole new table. Just a thought. :rose: Good luck
How would I do what I described with a view? I am still learning with SQL server?
-
I have a sql table with following schema. DateStamp BatchID MixName MixComment BatchDestination BatchComment MixNo MixRev Ing1Name Ing1Size Ing1PRM Ing1AcceptedUnderTol Ing1SPCSample Ing1TargetWgt Ing1ActualWgt Ing1PartialActualWgt Ing1PreactWgt Ing1SlowFeedTime Ing1FastFeedTime Ing2Name Ing2Size Ing2PRM Ing2AcceptedUnderTol Ing2SPCSample Ing2TargetWgt Ing2ActualWgt Ing2PartialActualWgt Ing2PreactWgt Ing2SlowFeedTime Ing2FastFeedTime Ing3Name Ing3Size Ing3PRM Ing3AcceptedUnderTol Ing3SPCSample Ing3TargetWgt Ing3ActualWgt Ing3PartialActualWgt Ing3PreactWgt Ing3SlowFeedTime Ing3FastFeedTime Ing4Name Ing4Size Ing4PRM Ing4AcceptedUnderTol Ing4SPCSample Ing4TargetWgt Ing4ActualWgt Ing4PartialActualWgt Ing4PreactWgt Ing4SlowFeedTime Ing4FastFeedTime Ing5Name Ing5Size Ing5PRM Ing5AcceptedUnderTol Ing5SPCSample Ing5TargetWgt Ing5ActualWgt Ing5PartialActualWgt Ing5PreactWgt Ing5SlowFeedTime Ing5FastFeedTime Ing6Name Ing6Size Ing6PRM Ing6AcceptedUnderTol Ing6SPCSample Ing6TargetWgt Ing6ActualWgt Ing6PartialActualWgt Ing6PreactWgt Ing6SlowFeedTime Ing6FastFeedTime Ing7Name Ing7Size Ing7PRM Ing7AcceptedUnderTol Ing7SPCSample Ing7TargetWgt Ing7ActualWgt Ing7PartialActualWgt Ing7PreactWgt Ing7SlowFeedTime Ing7FastFeedTime Ing8Name Ing8Size Ing8PRM Ing8AcceptedUnderTol Ing8SPCSample Ing8TargetWgt Ing8ActualWgt Ing8PartialActualWgt Ing8PreactWgt Ing8SlowFeedTime Ing8FastFeedTime Ing9Name Ing9Size Ing9PRM Ing9AcceptedUnderTol Ing9SPCSample Ing9TargetWgt Ing9ActualWgt Ing9PartialActualWgt Ing9PreactWgt Ing9SlowFeedTime Ing9FastFeedTime Ing10Name Ing10Size Ing10PRM Ing10AcceptedUnderTol Ing10SPCSample Ing10TargetWgt Ing10ActualWgt Ing10PartialActualWgt Ing10PreactWgt Ing10SlowFeedTime Ing10FastFeedTime Ing11Name Ing11Size Ing11PRM Ing11AcceptedUnderTol Ing11SPCSample Ing11TargetWgt Ing11ActualWgt Ing11PartialActualWgt Ing11PreactWgt Ing11SlowFeedTime Ing11FastFeedTime Ing12Name Ing12Size Ing12PRM Ing12AcceptedUnderTol Ing12SPCSample Ing12TargetWgt Ing12ActualWgt Ing12PartialActualWgt Ing12PreactWgt Ing12SlowFeedTime Ing12FastFeedTime TimeStarted TimeComp ManIntervention HA_Ing1Name HA_Ing1Size HA_Ing1PRM HA_Ing1TargetWgt HA_Ing1ActualWgt HA_Ing2Name HA_Ing2Size HA_Ing2PRM HA_Ing2TargetWgt HA_Ing2ActualWgt HA_Ing3Name HA_Ing3Size HA_Ing3PRM HA_Ing3TargetWgt HA_Ing3ActualWgt HA_Ing4Name HA_Ing4Size HA_Ing4PRM HA_Ing4TargetWgt HA_Ing4ActualWgt HA_Ing5Name HA_Ing5Size HA_I
The keyword you're looking for is
UNPIVOT. What database are you using?Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
I have a sql table with following schema. DateStamp BatchID MixName MixComment BatchDestination BatchComment MixNo MixRev Ing1Name Ing1Size Ing1PRM Ing1AcceptedUnderTol Ing1SPCSample Ing1TargetWgt Ing1ActualWgt Ing1PartialActualWgt Ing1PreactWgt Ing1SlowFeedTime Ing1FastFeedTime Ing2Name Ing2Size Ing2PRM Ing2AcceptedUnderTol Ing2SPCSample Ing2TargetWgt Ing2ActualWgt Ing2PartialActualWgt Ing2PreactWgt Ing2SlowFeedTime Ing2FastFeedTime Ing3Name Ing3Size Ing3PRM Ing3AcceptedUnderTol Ing3SPCSample Ing3TargetWgt Ing3ActualWgt Ing3PartialActualWgt Ing3PreactWgt Ing3SlowFeedTime Ing3FastFeedTime Ing4Name Ing4Size Ing4PRM Ing4AcceptedUnderTol Ing4SPCSample Ing4TargetWgt Ing4ActualWgt Ing4PartialActualWgt Ing4PreactWgt Ing4SlowFeedTime Ing4FastFeedTime Ing5Name Ing5Size Ing5PRM Ing5AcceptedUnderTol Ing5SPCSample Ing5TargetWgt Ing5ActualWgt Ing5PartialActualWgt Ing5PreactWgt Ing5SlowFeedTime Ing5FastFeedTime Ing6Name Ing6Size Ing6PRM Ing6AcceptedUnderTol Ing6SPCSample Ing6TargetWgt Ing6ActualWgt Ing6PartialActualWgt Ing6PreactWgt Ing6SlowFeedTime Ing6FastFeedTime Ing7Name Ing7Size Ing7PRM Ing7AcceptedUnderTol Ing7SPCSample Ing7TargetWgt Ing7ActualWgt Ing7PartialActualWgt Ing7PreactWgt Ing7SlowFeedTime Ing7FastFeedTime Ing8Name Ing8Size Ing8PRM Ing8AcceptedUnderTol Ing8SPCSample Ing8TargetWgt Ing8ActualWgt Ing8PartialActualWgt Ing8PreactWgt Ing8SlowFeedTime Ing8FastFeedTime Ing9Name Ing9Size Ing9PRM Ing9AcceptedUnderTol Ing9SPCSample Ing9TargetWgt Ing9ActualWgt Ing9PartialActualWgt Ing9PreactWgt Ing9SlowFeedTime Ing9FastFeedTime Ing10Name Ing10Size Ing10PRM Ing10AcceptedUnderTol Ing10SPCSample Ing10TargetWgt Ing10ActualWgt Ing10PartialActualWgt Ing10PreactWgt Ing10SlowFeedTime Ing10FastFeedTime Ing11Name Ing11Size Ing11PRM Ing11AcceptedUnderTol Ing11SPCSample Ing11TargetWgt Ing11ActualWgt Ing11PartialActualWgt Ing11PreactWgt Ing11SlowFeedTime Ing11FastFeedTime Ing12Name Ing12Size Ing12PRM Ing12AcceptedUnderTol Ing12SPCSample Ing12TargetWgt Ing12ActualWgt Ing12PartialActualWgt Ing12PreactWgt Ing12SlowFeedTime Ing12FastFeedTime TimeStarted TimeComp ManIntervention HA_Ing1Name HA_Ing1Size HA_Ing1PRM HA_Ing1TargetWgt HA_Ing1ActualWgt HA_Ing2Name HA_Ing2Size HA_Ing2PRM HA_Ing2TargetWgt HA_Ing2ActualWgt HA_Ing3Name HA_Ing3Size HA_Ing3PRM HA_Ing3TargetWgt HA_Ing3ActualWgt HA_Ing4Name HA_Ing4Size HA_Ing4PRM HA_Ing4TargetWgt HA_Ing4ActualWgt HA_Ing5Name HA_Ing5Size HA_I
What a classic, this is the most basic of error in database design and now you have to deal with the issue. Is the change in structure going to be a permanent change to the data structure or is it just to facilitate some reporting requirement? I would go with the view solution if it is for reporting. A series (16 of them) of UNION queries would do the job but t would be ugly.
Never underestimate the power of human stupidity RAH
-
What a classic, this is the most basic of error in database design and now you have to deal with the issue. Is the change in structure going to be a permanent change to the data structure or is it just to facilitate some reporting requirement? I would go with the view solution if it is for reporting. A series (16 of them) of UNION queries would do the job but t would be ugly.
Never underestimate the power of human stupidity RAH
This will be used only for reporting purposes.
-
This will be used only for reporting purposes.
Then go with the view idea
SELECT commonfields,
Ing1Name AS fieldanme,
Ing1Size,
Ing1PRM,
Ing1AcceptedUnderTol,
Ing1SPCSample,
Ing1TargetWgt,
Ing1ActualWgt,
Ing1PartialActualWgt,
Ing1PreactWgt,
Ing1SlowFeedTime,
Ing1FastFeedTime
FROM SomTable
UNION ALL
SELECT commonfields,
Ing2Name,
Ing2Size,
Ing2PRM,
Ing2AcceptedUnderTol,
Ing2SPCSample,
Ing2TargetWgt,
Ing2ActualWgt,
Ing2PartialActualWgt,
Ing2PreactWgt,
Ing2SlowFeedTime,
Ing2FastFeedTime
FROM SomTableNever underestimate the power of human stupidity RAH