英文:
SQL Pivot or Unpivot
问题
以下是翻译好的部分：
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 its 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
英文:
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
答案1
得分: 1
需要将Bone_free别名添加到UNPIVOT FOR列表中：
如果旋转列具有不同的数据类型，那么您需要将它们转换为一个数据类型（例如VARCHAR）。
SELECT
*
FROM
(
SELECT
CONVERT(VARCHAR(10), CheckTime,101) AS 日期
,DATEPART(hh, CheckTime) AS '检查小时'
,CAST(KneeCapValue AS DECIMAL) AS '膝盖'
,CAST(AttachedBackValue AS DECIMAL) AS '附着背部'
,CAST(BackSliverValue AS DECIMAL) AS '背部银'
,CAST(HardCartilageValue AS DECIMAL) AS '硬软骨'
,CAST(BoneValue AS DECIMAL) AS '骨头'
,CAST(BoneFree AS DECIMAL) AS '无骨'
,Machine
FROM DarkMeatFinishedProduct
) AS 源
UNPIVOT(Value FOR Measure IN (膝盖, 附着背部, 背部银, 硬软骨, 骨头, 无骨)) AS Pvt_Tble
希望这对你有帮助。
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论