How to get values of rows and columns

Question

I have two tables.

1. Student Table

2. Property Table

3. Result Table

How can I get the value of Student Table and the property ID of the column fron the Property table and merge that into the Result table?

Any advice would be helpful.

Update #1:

I tried using Christian Moen 's suggestion, this is what i get.

Answer 1

Other option is to use of apply if you don't want to go unpivot way

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.Property

Answer 2

You need to UNPIVOT the Student's columns first, to get the columns (properties names) in one column as rows. Then join with the Property table based on the property name like this:

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;

For the first ID, I used the ranking function ROW_NUMBER() to generate this sequence id.

---

This will give the exact results that you are looking for.

Results:

| 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 |