sql-server-2008 - Display row of another table as column of current table

Question

Consider there are two tables: Table1:

**Result          Total**
Pass             102
Fail              3
Undetermined      1

Table 2:

**Pass%   Fail%   Undetermined%**   
  96.23   2.83    0.94  

Result Needed:

**Result     Total  Percentage**
Pass          102       96.23
Fail           3        2.83
Undetermined   1        0.94

How to convert the table 2 rows as column in table 1 to obtain the result ?

标签： sql-server-2008

Answer 1

first, You can try to do unpivot on Table2, then JOIN with Table1.

Your sql-server version is 2008, you can use unpivot by UNION ALL.

CREATE TABLE T1(
  Result  VARCHAR(50),
  Total int
);
CREATE TABLE T2(
  Pass  FLOAT,
  Fail FLOAT,
  Undetermined FLOAT
);

insert into T2 VALUES (96.23,2.83,0.94)



INSERT INTO T1 VALUES ('Pass',102);
INSERT INTO T1 VALUES ('Fail',3);
INSERT INTO T1 VALUES ('Undetermined',1);

Query 1:

SELECT t1.*,s.val
FROM ( 
  SELECT Pass val,'PASS' Name
  FROM T2 
  UNION ALL
  SELECT Fail val,'Fail' Name
  FROM T2 
  UNION ALL
  SELECT Undetermined val,'Undetermined' Name
  FROM T2 
) s inner join T1 t1 on t1.Result = s.Name

Results:

|       Result | Total |   val |
|--------------|-------|-------|
|         Pass |   102 | 96.23 |
|         Fail |     3 |  2.83 |
| Undetermined |     1 |  0.94 |

---

If you can use CROSS APPLY with VALUE you can try this.

Query:

SELECT t1.*,s.val
FROM (
  SELECT v.* FROM T2 
  CROSS APPLY(VALUES 
       (Pass,'PASS'),
       (Fail,'Fail'),
       (Undetermined,'Undetermined')
  ) v(val,Name)
) s inner join T1 t1 on t1.Result = s.Name

Results:

|       Result | Total |   val |
|--------------|-------|-------|
|         Pass |   102 | 96.23 |
|         Fail |     3 |  2.83 |
| Undetermined |     1 |  0.94 |