SQL Server Query For Getting data in Specific Format

Question

We have two tables. One is PriceGroupTable and other is PricesTable. There is a one-to-many relationship between them. Please refer to the attached pic for more details.

I am looking for a SQL query which will give the result in the specific format which is highlighted in yellow in the attached pic.

Answer 1

Here is an example.

        Declare @PriceGroup TABLE
    (   id INT
        ,Catalog_Code INT
        ,Name VARCHAR(50)
    )

    INSERT INTO @PriceGroup (id,Catalog_Code,Name)
    VALUES (1,1,'PriceGroup1'),(2,1,'PriceGroup2'),(3,1,'PriceGroup3')

    Declare @Price TABLE
    (
        id INT
        ,articol_id INT
        ,catalog_code INT
        ,price_group_ID INT
        ,price DECIMAL(10,2)
    )

    INSERT INTO @Price (id,articol_id,catalog_code,price_group_ID,price)
    VALUES  (89,57,1,1,11.01)
            ,(90,57,1,2,11.01)
            ,(91,58,1,3,11.01)

    ;WITH PriceSource
    AS (
        SELECT 
            articol_id
            ,Name
            ,price
        FROM
            @Price as P
            INNER JOIN @PriceGroup AS PG
            ON P.price_group_ID  = PG.id
            AND P.catalog_code = PG.Catalog_Code 
    )

    SELECT *
    FROM PriceSource AS PS
    PIVOT
    (   MAX(Price)
        FOR Name IN ([PriceGroup1],[PriceGroup2],[PriceGroup3])
    )P

and the output:

articol_id  PriceGroup1 PriceGroup2 PriceGroup3
57  11.01   11.01   NULL
58  NULL    NULL    11.01