Query to select data from row into column in SQL Server

Question

I have example table like this :

type          value
-----------------------
Name      |   John
Gender    |   Male
Address   |   New City
Phone     |   62813 
etc...    |   etc...

I want to make query and show the data from row into column, and the expected result like this :

Name    Gender    Address     Phone     etc...
----------------------------------------------
John  |  Male  |  New City |  62813  |  etc...

In this scenario etc... means there are more data in the table. Is there any solution to show the table from row into column with many data in the table?

Answer 1

You would need to use Pivoting to solve this type of Problem.

Lets create a small demo table T2 and proceed from it.

Create table T2 (type varchar(100),Value varchar(100));
insert into T2 Values('Name','John');
insert into T2 Values('Gender','Male');
insert into T2 Values('Address','New City');
insert into T2 Values('Phone','62813');

Now if this is the exact number then we can hard code column value of type and use it in our sql as shown below.

SELECT [Name]
    ,[Gender]
    ,[Address]
    ,[Phone]
FROM (
    SELECT type
        ,Value
    FROM T2
    ) AS T1
PIVOT(Min(Value) FOR type IN (
            [Name]
            ,[Gender]
            ,[Address]
            ,[Phone]
            )) PVT;

But if there are lot of rows in your table and you want it to be dynamic then you can use dynamic query to handle such type of problems.

DECLARE @dml AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(type)
FROM (
    SELECT type
    FROM T2
    ) AS T1;

--Prepare the PIVOT query using the dynamic 
SET @dml = N'SELECT ' + @ColumnName + ' FROM
  (
  SELECT type,
        Value
 FROM T2
) AS T1
PIVOT (Min(Value)
FOR type IN (' + @ColumnName + ')) AS PVT'

--Print @DynamicPivotQuery
--Execute the Dynamic Pivot Query
EXEC sp_executesql @dml

This will give you the desired result.