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?

This is usually a bad idea. You're usually _so much better off_ handling this kind of in the client code or reporting tool. However, if you must, you should also know the SQL language has a _very strict requirement_ for the number of and types of the result columns to be known at query compile time, _before looking at any data in your tables_. — Joel Coehoorn, Nov 15 '21 at 18:10
This design is called EAV - Entity Attribute Value - suggest you research that and reconsider this design if possible. Every query that needs to retrieve useful information from this table will pay the cost for your "ultimate flexibility" and they will not be "efficient" as the number of rows grows. — SMor, Nov 15 '21 at 18:12

score 1 · Accepted Answer · answered Nov 15 '21 at 19:09

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.

Query to select data from row into column in SQL Server

1 Answers1