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.

enter image description here
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.

• Sabin B: Are there only PriceGroup 1,2,3 ? Can be more then 3? If NO, then you can use pivot with static columns. Else use dynamic pivot.
• Rakesh Gaur: Hello Sabin: Price group can be more than 3. Please guide me how to handle that situation
• Sabin B: Here is an example of it: dba.stackexchange.com/questions/84200/…
• Sabin B: Also, you can check these ones : sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server mssqltips.com/sqlservertip/2783/… stackoverflow.com/questions/10404348/…

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