Converting Row Value as Column Name

Converting Row Value as Column Name

      Hi, This is my first article in this blog. I would like to share the problem I faced in my work and the things I find differently, interestingly in my technical life... :)

Problem

       In some real time scenario, we may have the column name and its value in the same row, But when we want to display the data, we would like to display a row value as column and display the value for that column.

Ex: Here we have some student records.

Here we have a column 'ColumnName' which contain the values to be converted as the column.
Column 'Value' contains the respective value for the column.

Expected output is : 

Query

   Below is the query I used to get the above output.
       

 SELECT ID,MIN(CASE ColumnName WHEN 'Name' THEN ISNULL(Value,'') END) AS Name

  ,MIN(CASE ColumnName WHEN 'Age' THEN ISNULL(Value,'') END) AS Age

 FROM StudentRecords

 GROUP BY ID

Explanation
    
     We are checking all the possible ColumnName values in the case statement and display the value, we are using the ColumnName in the alias name.

Full Script used in this article is below...

CREATE TABLE StudentRecords(

    ID INT

    ,ColumnName VARCHAR(50)

    ,Value VARCHAR(50)

)

GO

INSERT INTO StudentRecords

VALUES(1,'Name','Joe')

INSERT INTO StudentRecords

VALUES(1,'Age','21')

INSERT INTO StudentRecords

VALUES(2,'Name','Ram')

INSERT INTO StudentRecords

VALUES(2,'Age','25')

INSERT INTO StudentRecords

VALUES(3,'Name','Satheesh')

INSERT INTO StudentRecords

VALUES(3,'Age','22')

INSERT INTO StudentRecords

VALUES(4,'Name','Kathir')

INSERT INTO StudentRecords

VALUES(4,'Age','12')

GO

SELECT ID,MIN(CASE ColumnName WHEN 'Name' THEN ISNULL(Value,'') END) AS Name

  ,MIN(CASE ColumnName WHEN 'Age' THEN ISNULL(Value,'') END) AS Age

 FROM StudentRecords

 GROUP BY ID