Transpose rows into columns based on ID.

Question

I have a table with two columns, ProductionEventId and Value. The ProductionEventId column contains unique ids that repeat for each value in the Value column. See below.

How can I have one occurrence of the ProductionEventId with the multiple values in their own columns? Basically I need to transpose the Value Column into multiple columns where the rows all share the same ProductionEventId

ProductionEventId	Name	Value
52591	Length	291
52591	Thickness	30
52591	Width	72
52591	BatchId	ABC-DEF1G-24NOV12-9h
52592	Length	284
52592	Thickness	29
52592	Width	74
52592	BatchId	ABC-DEF1G-24NOV12-11h

Answer 1

this is a pivot. the trick is you need to know the max number of rows to pivot. your example shows 4.

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

select * 
from (
    select 
        ProductionEventId, 
        value, 
        row_number() over (PARTITION BY ProductionEventId ORDER BY value) as rownum
    from MyTable
) as e
PIVOT
(
    max(value)
    for rownum in ([1],[2],[3],[4])
) p

Answer 2

Hi @Michael Deaton

How can I have one occurrence of the ProductionEventId with the multiple values in their own columns?

Typically, you need a third column to distinguish each row of one ProductionEventId. As answered above, you could use Row_Number() function. And find the max number of rows for single ProductionEventId.If not familiar with pivot, you could also write query like this:

;WITH CTE AS
(
 SELECT ProductionEventId, 
        value, 
        ROW_NUMBER()OVER(PARTITION BY ProductionEventId ORDER BY value) AS RNum
 FROM YourTable
)
SELECT ProductionEventId
      ,MAX(CASE WHEN RNum = 1 THEN value ELSE NULL END) AS VALUE1
	  ,MAX(CASE WHEN RNum = 2 THEN value ELSE NULL END) AS VALUE2
	  ,MAX(CASE WHEN RNum = 3 THEN value ELSE NULL END) AS VALUE3
	  ,MAX(CASE WHEN RNum = 4 THEN value ELSE NULL END) AS VALUE4
FROM CTE
GROUP BY ProductionEventId

Besides, if you're not attached to multiple columns, you can also consider comma-separated strings.

SELECT ProductionEventId
      ,STRING_AGG (value, ',') AS CommaSeparatedValues
FROM YourTable
GROUP BY ProductionEventId

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".