0

I'm looking to convert row values into columns using SQL WITHOUT using pivot or dynamic SQL. (The database we are using doesn't support these.)

This would be example data:

Id   ColumnName   Value
100  FirstName    Ted
100  LastName     Bundy
100  EmployeeID   4333
101  FirstName    John
101  LastName     Snow
101  EmployeeID   4177

And I want to convert this data into the following structure:

ID   FirstName    LastName    EmployeeID
100  Ted          Bundy       4333
101  John         Snow        4177

There will also be far more IDs than 2, but this is the general idea. I've tried using all solutions in: Efficiently convert rows to columns in sql server, but the solutions only work for 1 record. In addition, I've tried many combinations of JOINS, but I can't get the data to format correctly.

I'm hoping to do this as efficiently as possible, since it will be used constantly to format data. Any help is appreciated.

Community
  • 1
  • 1
EnterTheCode
  • 474
  • 5
  • 20

1 Answers1

1

You can do it using conditional aggregation, which is standard ANSI SQL:

SELECT Id,
       MAX(CASE WHEN ColumnName = 'FirstName' THEN Value END) AS FirstName,
       MAX(CASE WHEN ColumnName = 'LastName' THEN Value END) AS LastName,
       MAX(CASE WHEN ColumnName = 'EmployeeID' THEN Value END) AS EmployeeID    
FROM mytable
GROUP BY Id

Demo here

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98