SQL Pivot or Unpivot

Question

I am trying to convert some data from one table format to another. I've never used either the Pivot or Unpivot functions and even after watching a few videos and looking at examples I'm still confused. Here is the query that gets the base data.

SELECT
   CONVERT(VARCHAR(10), CheckTime,101) AS Date
  ,DATEPART(hh, CheckTime) AS 'Check_Hour'
  ,KneeCapValue AS 'Knee_Cap'
  ,AttachedBackValue AS 'Attached_Back'
  ,BackSliverValue AS 'Back_Sliver'
  ,HardCartilageValue AS 'Hard_Cartilage'
  ,BoneValue AS 'Bone'
  ,BoneFree AS 'Bone_Free'
  ,Machine 
FROM DarkMeatFinishedProduct

I'm trying to get the data into the following format.

[Date], [Check Hour], [Machine], [Measure](This will be the old column names such as Knee Cap, Attached Back etc...) [Value]

I've gotten close with the following SQL but I'm not quite sure why it's returning a column for Bone_Free instead of making that part of the [Measure] and placing it's values in the [Value] Column. Here's as far as I've gotten so far.

SELECT
  *
FROM
(
  SELECT
     CONVERT(VARCHAR(10), CheckTime,101) AS Date
    ,DATEPART(hh, CheckTime) AS 'Check_Hour'
    ,KneeCapValue AS 'Knee_Cap'
    ,AttachedBackValue AS 'Attached_Back'
    ,BackSliverValue AS 'Back_Sliver'
    ,HardCartilageValue AS 'Hard_Cartilage'
    ,BoneValue AS 'Bone'
    ,BoneFree AS 'Bone_Free'
    ,Machine 
  FROM DarkMeatFinishedProduct
) AS Source
Unpivot(Value FOR Measure IN (Knee_Cap, Attached_Back, Back_Sliver, Hard_Cartilage, Bone)) AS Pvt_Tble

Answer 1

You need to add the Bone_free alias to the UNPIVOT FOR list :

If you the pivoted columns had deafferents datatypes then you will have to convert them into one datatype (VARCHAR for exemple).

SELECT
  *
FROM
(
  SELECT
     CONVERT(VARCHAR(10), CheckTime,101) AS Date
    ,DATEPART(hh, CheckTime) AS 'Check_Hour'
    ,CAST(KneeCapValue AS DECIMAL) AS 'Knee_Cap'
    ,CAST(AttachedBackValue AS DECIMAL) AS 'Attached_Back'
    ,CAST(BackSliverValue AS DECIMAL) AS 'Back_Sliver'
    ,CAST(HardCartilageValue AS DECIMAL) AS 'Hard_Cartilage'
    ,CAST(BoneValue AS DECIMAL) AS 'Bone'
    ,CAST(BoneFree AS DECIMAL) AS 'Bone_Free'
    ,Machine 
  FROM DarkMeatFinishedProduct
) AS Source
Unpivot(Value FOR Measure IN (Knee_Cap, Attached_Back, Back_Sliver, Hard_Cartilage, Bone, Bone_Free)) AS Pvt_Tble