SQL Server multiple rows and two columns into single row with multiple columns

Question

I have a table with a columns for case ID, Action, and reason. a single case ID can have multiple rows with different actions and codes. I can pivot and get multiple rows with columns action1, action2, action3, etc., but for the life of me, can't get case id, action1, reason1, action2, reason2, etc on a single row.

Answer 1

If you need to go a little more dynamic (n reasons)

Drop Table #Temp
Declare @YourTable table (ID int,Action varchar(50),Reason varchar(50))
Insert Into @YourTable values
(1,'Load Data','Boss said to'),
(1,'Run Query','It is what I do'),
(2,'Take Garbage Out','Wife makes me')


-- Convert Data to EAV Structure'ish
Declare @XML xml = (Select *,GrpSeq = Row_Number() over (Partition By ID Order By (Select NULL)) from @YourTable for XML RAW)
Select ID      = r.value('@ID','int')
      ,ColSeq  = Row_Number() over (Partition By r.value('@ID','int') Order By (Select NULL))
      ,Element    = attr.value('local-name(.)','varchar(100)')+r.value('@GrpSeq','varchar(10)')
      ,Value      = attr.value('.','varchar(max)') 
 Into  #Temp
 From  @XML.nodes('/row') as A(r)
 Cross Apply A.r.nodes('./@*') AS B(attr)
 Where attr.value('local-name(.)','varchar(100)') not in ('ID','GrpSeq')

-- Get Cols in correct Order
Declare @Cols varchar(max) 
Set @Cols = Stuff((Select ',' + QuoteName(Element) 
                    From  (Select Distinct Top 100 Percent  ColSeq,Element From #Temp Order By ColSeq ) A
                    For XML Path(''), Type
                   ).value('.', 'varchar(max)'),1,1,'')

-- Execute Dynamic Pivot
Declare @SQL varchar(max) = '
Select *
 From (Select ID,Element,Value From #Temp) T
 Pivot (
        max(Value)
        For [Element] in (' + @Cols + ')
       ) P '

Exec(@SQL)

Returns