CODEWITHSUNDEEP
sqlsql-serverpivot
Dev
Dev

Reputation: 367

Convert three rows values into columns, NOT as comma separated value

I have table structure like

select catalog_item_id,metal_type,metal_color 
from catalog_item_castings 
where catalog_Item_Id =465173

It returns output as:

enter image description here

And I want output as:

enter image description here

And I want to insert this data into new temp table in SQL Server.

Thanks in advance.

Upvotes: 0

Views: 55

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can use Conditional Aggregation within a Dynamic Pivot Statement in order to include all distinct combinations of the columns [metal_type] and [metal_color], even different values for combinations are inserted in the future :

DECLARE @cols  AS NVARCHAR(MAX),  @query AS NVARCHAR(MAX)

SELECT @cols = (SELECT STRING_AGG(CONCAT('MAX(CASE WHEN [dr]=',dr,
                                         ' THEN CONCAT([metal_type],''/'',[metal_color]) END) AS [Casting_',dr,']'),',') 
                           WITHIN GROUP ( ORDER BY dr )
                  FROM 
                  ( 
                   SELECT DISTINCT 
                          DENSE_RANK() OVER 
                          (PARTITION BY [catalog_item_id]
                               ORDER BY CONCAT([metal_type],[metal_color])) AS dr
                     FROM [catalog_item_castings] ) c);

SET  @query = 
 'SELECT [catalog_item_id],'+ @cols + 
 ' FROM 
  (
   SELECT *, DENSE_RANK() OVER 
           ( PARTITION BY [catalog_item_id] 
                 ORDER BY CONCAT([metal_type], [metal_color]) ) AS dr
     FROM [catalog_item_castings]
  ) c
 GROUP BY [catalog_item_id]';

EXEC sp_executesql @query;

Demo

Upvotes: 0

Zhorov
Zhorov

Reputation: 29943

Conditional aggregation is an option:

SELECT
   catalog_item_id,
   MAX(CASE WHEN rn % 3 = 1 THEN CONCAT(metal_type, '/', metal_color) END) AS Casting_1,
   MAX(CASE WHEN rn % 3 = 2 THEN CONCAT(metal_type, '/', metal_color) END) AS Casting_2,
   MAX(CASE WHEN rn % 3 = 0 THEN CONCAT(metal_type, '/', metal_color) END) AS Casting_3
FROM (
   SELECT 
      catalog_item_id, metal_type, metal_color, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
   FROM (VALUES
      (465173, 'na', 'METALCOLOR'),
      (465173, 'na', 'METAL-001'),
      (465173, 'na', 'na')
   ) catalog_item_castings (catalog_item_id, metal_type, metal_color) 
   WHERE catalog_Item_Id = 465173
) t   
GROUP BY catalog_item_id
-- or if you have more than three rows per [catalog_item_id]
-- GROUP BY catalog_item_id, (rn - 1) / 3

Result:

catalog_item_id Casting_1     Casting_2    Casting_3
-------------------------------------------------
465173          na/METALCOLOR na/METAL-001 na/na

Upvotes: 1

Related Questions