Creating query to return Parameter and Parameter values in single result

Question

How do I consolidate/transpose the following tables into one record per customer but having the parameters as columns?

4 tables:

• Company
• Attribute
• Company_Attribute
• Company_Attribute_Values

Source Tables:

Desired Output:

Answer 1

Looks like have two questions Join the tables and Create the PIVOT.

I will help you with the join

SELECT C.Name, A.Title, CAV.Value
FROM Company C
JOIN Company_Attribute_Values CAV
  ON C.id = CAV.company_id
JOIN Attribute A
  ON CAV.attribute_id = A.id

SQL Fiddle Demo

|     Name |          Title |    Value |
|----------|----------------|----------|
| ABC Corp |       Currency |      USD |
| ABC Corp |         Reg No |   Reg123 |
| ABC Corp |         Vat No |  Vat5153 |
| ABC Corp | AccountManager | Mr J Doe |
| XYZ Corp |       Currency |      EUR |
| XYZ Corp |         Vat No |   VAT654 |
|     ACME |       Currency |      EUR |
|     ACME |         Reg No |   Reg567 |
|     ACME |         Vat No |   Vat213 |

btw doesnt make sense you have two table for store same data. Company_Attribute is the same table as Company_Attribute_Values less one field. So just remove the first one.

PIVOT part

WITH cte as (
    SELECT C.Name, A.Title, CAV.Value
    FROM Company C
    JOIN Company_Attribute_Values CAV
      ON C.id = CAV.company_id
    JOIN Attribute A
      ON CAV.attribute_id = A.id
)
SELECT name, 
    [Currency], [Reg No], [Vat No], [AccountManager]
FROM
    cte
PIVOT
(
MAX([Value])
FOR Title IN ([Currency], [Reg No], [Vat No], [AccountManager])
) AS PivotTable;

OUTPUT

|     name | Currency | Reg No |  Vat No | AccountManager |
|----------|----------|--------|---------|----------------|
| ABC Corp |      USD | Reg123 | Vat5153 |       Mr J Doe |
|     ACME |      EUR | Reg567 |  Vat213 |         (null) |
| XYZ Corp |      EUR | (null) |  VAT654 |         (null) |