Viewing SQL Server while trying to use the same columns multiple times

Question

I have a table called "computers" with the following columns:
row_id
computer_id
key
value

The value for the line where the key is "3" contains the name of the computer.
The value for the line where the key is "4" contains the IP address of the computer.
The value for the line where the key is "5" contains the computer user.

Computer_id is a unique unique computer.

I would like to create a view that will have the following columns:
computer_id
name
ip_address
user

I am afraid how to do it. I am sure that this may not be so difficult, but with difficulty trying to refer to the same columns (key, value) several times.
Thanks in advance.

Answer 1

You can group the computer ID and choose the right keys using max and case. For instance:

select computer_id , max(case when key = 3 then value end) as Name , max(case when key = 4 then value end) as IP , max(case when key = 5 then value end) as User from YourTable group by computer_id 

Answer 2

 CREATE VIEW your_view AS SELECT x.computer_id, x.value AS name, y.value AS ip_address, z.value AS user FROM computers AS x INNER JOIN computers AS y ON x.computer_id = y.computer_id INNER JOIN computers AS z ON x.computer_id = z.computer_id WHERE x.key = 3 AND y.key = 4 AND z.key = 5 

Answer 3

Another solution is subqueries:

 SELECT computer_id, value AS 'name', (SELECT value FROM computers sqi WHERE sqi.computer_id = computers.computer_id AND sqi.key = 4) AS 'ip', (SELECT value FROM computers squ WHERE squ.computer_id = computers.computer_id AND squ.key = 5) AS 'user' FROM computers WHERE key = 3 

Answer 4

 select computer_id, max(case when [KEY] = 3 then value end) as Name, max(case when [KEY] = 4 then value end) as IP, max(case when [KEY] = 5 then value end) as [User] from YourTable group by computer_id 

Answer 5

What you request is a table that follows the design of the EAV. Here's an article with some examples on how EAVs can / should be requested.

Here is an example of a subquery to define your table:

 SELECT x.Computer_Id, (SELECT value FROM Computers AS a WHERE x.Computer_Id = a.Computer_Id AND [key]= 3) AS 'Name', (SELECT value FROM Computers AS b WHERE x.Computer_Id = b.Computer_Id AND [key]= 4) AS 'IpAddress', (SELECT value FROM Computers AS c WHERE x.Computer_Id = c.Computer_Id AND [key]= 5) AS 'User' FROM Computers as x GROUP BY x.Computer_Id