我想要的输出如下 [英] I want the ouput as follows
本文介绍了我想要的输出如下的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有3张桌子如下
表1:
UnitTypeId UnitType
1 Rolls
2 Sheets
3 Bobbins
表2:
AttributeId属性
1宽度
2直径
3评论
4核心尺寸
5长度
6直径类型
7核心类型
8 Grain_Direction
表3:
UnitTypeAttributeId UnitTypeId AttributeId DisplayAs
1 1 1 Roll Width
2 1 2 Roll Diameter
3 1 3 Roll Dia评论
4 1 4核心尺寸
5 2 1张宽度
6 2 5张长度
7 3 1梭芯宽度
8 3 5梭芯长度
9 1 6直径类型
10 1 7核心类型
11 2 8谷物目录
我想要输出如下
UnitType Attribute1 attribute2 attribute3 attribute4 attribute5
Rolls Roll width diameter core size core typr diametercmnts
床单宽度纸张长度M重量纹理方向
线轴梭芯宽度梭芯长度
盒子盒子宽度盒子长度
核心核心宽度核心长度核心内部尺寸核心壁厚核心切割类型
解决方案
试试这个:
DECLARE @ tab1 TABLE (UnitTypeId INT ,UnitType NVARCHAR ( 30 ))
INSERT INTO @ tab1 (UnitTypeId,UnitType)
VALUES ( 1 ,' Rolls')
INSERT INTO @ tab1 (UnitTypeId ,UnitType)
VALUES ( 2 ,' 表格)
INSERT INTO @ tab1 (UnitTypeId,UnitType)
VALUES ( 3 ,' Bobbins' )
DECLARE @ tab2 表(AttributeId INT ,属性 NVARCHAR ( 30 ))
INSERT INTO @ tab2 (AttributeId,Attribute)
VALUES ( 1 ,' Width')
INSERT INTO @ tab2 (AttributeId,Attribute)
VALUES ( 2 ,' Diameter')
INSERT INTO @ tab2 (AttributeId,Attribute)
VALUES ( 3 ,' 评论')
INSERT INTO @ tab2 (AttributeId,Attribute)
VALUES ( 4 ,' 核心大小')
INSERT INTO @ tab2 (AttributeId,Attribute)
VALUES ( 5 ,' Length')
INSERT INTO @ tab2 (AttributeId,Attribute)
VALUES ( 6 ,' 直径类型')
INSERT INTO @ tab2 (AttributeId,Attribute)
VALUES ( 7 ,' 核心类型')
INSERT < span class =code-keyword> INTO @ tab2 (AttributeId,Attribute)
VALUES ( 8 ,' Grain_Direction')
DECLARE @ tab3 TABLE (UnitTypeAttributeId INT ,UnitTypeId INT , AttributeId INT ,DisplayAs NVARCHAR ( 30 ) )
INSERT INTO @ tab3 (UnitTypeAttributeId,UnitTypeId,AttributeId,DisplayAs)
VALUES ( 1 , 1 , 1 ,' Roll Width')
INSERT INTO < span class =code-sdkkeyword> @ tab3 (UnitTypeAttributeId,UnitTypeId,AttributeId,DisplayAs)
VALUES ( 2 , 1 , 2 ,' Roll Diameter')
INSERT INTO @ tab3 (UnitTypeAttributeId,UnitTypeId,AttributeId,DisplayAs)
VALUES ( 3 , 1 , 3 ,' Roll Dia comments')
INSERT INTO @ tab3 (UnitTypeAttributeId,UnitTypeId,AttributeId,DisplayAs)
VALUES ( 4 , 1 , 4 ,' 核心大小')
INSERT INTO @ tab3 (UnitTypeAttributeId,UnitTypeId,AttributeId,DisplayAs)
VALUES ( 5 , 2 , 1 ,' 工作表宽度')
INSERT INTO @ tab3 ( UnitTypeAttributeId,UnitTypeId,AttributeId,DisplayAs)
VALUES ( 6 , 2 , 5 ,' 工作表长度')
INSERT INTO @ tab3 (UnitTypeAttributeId,UnitTypeId,AttributeId,DisplayAs)
VALUES ( 7 , 3 , 1 ,' 梭芯宽度')
INSERT INTO @ tab3 ( UnitTypeAttributeId,UnitTypeId,AttributeId,DisplayAs)
VALUES ( 8 , 3 , 5 ,' 梭芯长度')
INSERT INTO @ tab3 (UnitTypeAttributeId,UnitTypeId,AttributeId,DisplayAs)
VALUES ( 9 , 1 , 6 ,' Diameter Type')
INSERT INTO @ tab3 (UnitTypeAttributeId,UnitTypeId,AttributeId,DisplayAs)
VALUES ( 10 , 1 , 7 ,' 核心类型')
INSERT INTO @ tab3 (UnitTypeAttributeId,UnitTypeId,AttributeId,DisplayAs)
VALUES ( 11 , 2 , 8 ,' Grain Dir')
SELECT UnitType,[宽度],[直径],[Comme] nts],[Core Size],[Length],[Diameter Type ],[Core Type ], [Grain_Direction]
FROM (
SELECT t2.UnitType,t3.Attribute, t1.DisplayAs
FROM @ tab3 AS t1 INNER JOIN @ tab1 AS t2 ON t1.UnitTypeId = t2.UnitTypeId
INNER JOIN @ tab2 AS t3 ON t1.AttributeId = t3.AttributeId
) AS DT
PIVOT(MAX(DisplayAs) FOR 属性 IN ([宽度],[直径],[注释],[核心尺寸],[长度],[直径类型],[Core Type ],[Grain_Direction])) AS PT
I have 3 tables as follows
Table1:
UnitTypeId UnitType 1 Rolls 2 Sheets 3 Bobbins
Table2:
AttributeId Attribute 1 Width 2 Diameter 3 Comments 4 Core Size 5 Length 6 Diameter Type 7 Core Type 8 Grain_Direction
Table3:
UnitTypeAttributeId UnitTypeId AttributeId DisplayAs 1 1 1 Roll Width 2 1 2 Roll Diameter 3 1 3 Roll Dia comments 4 1 4 Core size 5 2 1 Sheet width 6 2 5 Sheet Length 7 3 1 Bobbin width 8 3 5 Bobbin Length 9 1 6 Diameter Type 10 1 7 Core Type 11 2 8 Grain Dir
And i want the output as follows
UnitType Attribute1 attribute2 attribute3 attribute4 attribute5
Rolls Roll width diameter core size core typr diametercmnts
sheets sheet width sheet length M weight grain direction
Bobbins bobbins width bobbins length
Boxes Boxes width Boxes length
Cores Cores width Cores length core inner dimension core wall thickness core cut types
解决方案
Try this:
DECLARE @tab1 TABLE(UnitTypeId INT, UnitType NVARCHAR(30)) INSERT INTO @tab1 (UnitTypeId, UnitType) VALUES(1, 'Rolls') INSERT INTO @tab1 (UnitTypeId, UnitType) VALUES(2, 'Sheets') INSERT INTO @tab1 (UnitTypeId, UnitType) VALUES(3, 'Bobbins') DECLARE @tab2 TABLE(AttributeId INT, Attribute NVARCHAR(30)) INSERT INTO @tab2 (AttributeId, Attribute) VALUES(1, 'Width') INSERT INTO @tab2 (AttributeId, Attribute) VALUES(2, 'Diameter') INSERT INTO @tab2 (AttributeId, Attribute) VALUES(3, 'Comments') INSERT INTO @tab2 (AttributeId, Attribute) VALUES(4, 'Core Size') INSERT INTO @tab2 (AttributeId, Attribute) VALUES(5, 'Length') INSERT INTO @tab2 (AttributeId, Attribute) VALUES(6, 'Diameter Type') INSERT INTO @tab2 (AttributeId, Attribute) VALUES(7, 'Core Type') INSERT INTO @tab2 (AttributeId, Attribute) VALUES(8, 'Grain_Direction') DECLARE @tab3 TABLE (UnitTypeAttributeId INT, UnitTypeId INT, AttributeId INT, DisplayAs NVARCHAR(30)) INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs) VALUES(1, 1, 1, 'Roll Width') INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs) VALUES(2, 1, 2, 'Roll Diameter') INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs) VALUES(3, 1, 3, 'Roll Dia comments') INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs) VALUES(4, 1, 4, 'Core size') INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs) VALUES(5, 2, 1, 'Sheet width') INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs) VALUES(6, 2, 5, 'Sheet Length') INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs) VALUES(7, 3, 1, 'Bobbin width') INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs) VALUES(8, 3, 5, 'Bobbin Length') INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs) VALUES(9, 1, 6, 'Diameter Type') INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs) VALUES(10, 1, 7, 'Core Type') INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs) VALUES(11, 2, 8, 'Grain Dir') SELECT UnitType, [Width], [Diameter], [Comments], [Core Size], [Length], [Diameter Type], [Core Type], [Grain_Direction] FROM ( SELECT t2.UnitType, t3.Attribute, t1.DisplayAs FROM @tab3 AS t1 INNER JOIN @tab1 AS t2 ON t1.UnitTypeId = t2.UnitTypeId INNER JOIN @tab2 AS t3 ON t1.AttributeId = t3.AttributeId ) AS DT PIVOT(MAX(DisplayAs) FOR Attribute IN ([Width], [Diameter], [Comments], [Core Size], [Length], [Diameter Type], [Core Type], [Grain_Direction])) AS PT
这篇关于我想要的输出如下的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文