我有两张桌子。



如何获得属性表中的“学生表”的值和列的属性ID,并将其合并到结果表中?
任何建议都会有帮助。
更新1:
我试着使用克里斯蒂安?莫恩的建议,这就是我所得到的。

发布于 2017-12-26 18:09:17
您需要首先UNPIVOT Student的列,才能将列(属性名称)作为行在一列中获取。然后,基于属性名的Property表的join如下所示:
WITH UnPivoted
AS
(
SELECT ID, value,col
FROM
(
SELECT ID,
CAST(Name AS NVARCHAR(50)) AS Name,
CAST(Class AS NVARCHAR(50)) AS Class,
CAST(ENG AS NVARCHAR(50)) AS ENG,
CAST(TAM AS NVARCHAR(50)) AS TAM,
CAST(HIN AS NVARCHAR(50)) AS HIN,
CAST(MAT AS NVARCHAR(50)) AS MAT,
CAST(PHY AS NVARCHAR(50)) AS PHY
FROM Student
) AS s
UNPIVOT
(value FOR col IN
([Name], [class], [ENG], [TAM], [HIN], [MAT], [PHY])
)AS unpvt
)
SELECT
ROW_NUMBER() OVER(ORDER BY u.ID,PropertyID) AS ID,
p.PropertyID,
u.Value,
u.ID AS StudID
FROM Property AS p
INNER JOIN UnPivoted AS u ON p.PropertyName = u.col;对于第一个ID,我使用排序函数ROW_NUMBER()生成这个序列id。
这将给出确切的结果,你正在寻找。
| ID | PropertyID | Value | StudID |
|----|------------|--------|--------|
| 1 | 1 | Jack | 1 |
| 2 | 2 | 10 | 1 |
| 3 | 3 | 89 | 1 |
| 4 | 4 | 88 | 1 |
| 5 | 5 | 45 | 1 |
| 6 | 6 | 100 | 1 |
| 7 | 7 | 98 | 1 |
| 8 | 1 | Jill | 2 |
| 9 | 2 | 10 | 2 |
| 10 | 3 | 89 | 2 |
| 11 | 4 | 99 | 2 |
| 12 | 5 | 100 | 2 |
| 13 | 6 | 78 | 2 |
| 14 | 7 | 91 | 2 |
| 15 | 1 | Trevor | 3 |
| 16 | 2 | 12 | 3 |
| 17 | 3 | 100 | 3 |
| 18 | 4 | 50 | 3 |
| 19 | 5 | 49 | 3 |
| 20 | 6 | 94 | 3 |
| 21 | 7 | 100 | 3 |
| 22 | 1 | Jim | 4 |
| 23 | 2 | 8 | 4 |
| 24 | 3 | 100 | 4 |
| 25 | 4 | 91 | 4 |
| 26 | 5 | 92 | 4 |
| 27 | 6 | 100 | 4 |
| 28 | 7 | 100 | 4 |发布于 2017-12-27 04:53:46
其他的选择是如果你不想用apply的方式使用unpivot
select row_number() over (order by (select 1)) ID, p.PropertyID [PropID], a.Value, a.StuID
from Student s
cross apply
(
values (s.ID, 'Name', s.Name),
(s.ID, 'Class', cast(s.Class as varchar)),
(s.ID, 'ENG', cast(s.ENG as varchar)),
(s.ID, 'TAM', cast(s.TAM as varchar)),
(s.ID, 'HIN', cast(s.HIN as varchar)),
(s.ID, 'MAT', cast(s.MAT as varchar)),
(s.ID, 'PHY', cast(s.PHY as varchar))
) as a(StuID, Property, Value)
join Property p on p.PropertyName = a.Propertyhttps://stackoverflow.com/questions/47981855
复制相似问题