Is SubQuery in Select Statement is A Good Choice for This Issue?

Vote count:

0

Assume I have two tables which structure like below:

Table People:

---------------------------------------------------
| Pepole |ClothBrandId  | ShoeBrandId | HatBrandId|
---------------------------------------------------
| Jo     | 1            | 2           | 3         |
---------------------------------------------------

Table Brand:

 -------------------------
 | BrandId |BrandName    |
 -------------------------
 | 1       | NBrand      |
 -------------------------
 | 2       | ABrand      |
 -------------------------
 | 3       | PBrand      |
 -------------------------

What I want is use T-SQL to generate a set as below:

    ---------------------------------------------------------
    | Pepole |ClothBrandName  | ShoeBrandName | HatBrandName|
    ---------------------------------------------------------
    | Jo     | NBrand         | ABrand        | PBrand      |
    ---------------------------------------------------------

Currently, I think subquery in select statement can resolve this:

 SELECT
      People,
      SELECT BrandName as ClothBrandName FROM Brand WHERE BrandId=ClothBrandId,
      SELECT BrandName as ShoeBrandName FROM Brand WHERE BrandId=ShoeBrandId,
      SELECT BrandName as HatBrandName FROM Brand WHERE BrandId=HatBrandId
FROM 
People

But I don't think it a efficient solution. Another is use UNPIVOT then PIVOT. Which will be hard to read. Is there other appropriate ways for this？

asked 51 secs ago

jojo

10