SQL Server view when trying to use the same columns multiple times

I have a table named 'computers' with the following cols:

row_id

computer_id

key

value

The value for a row where key equals '3' contains the name of the computer.

The value for a row where key equals '4' contains the IP of the computer.

The value for a row where key equals '5' contains the user of the computer.

Computer_id represents a specific unique computer.

I would like to create a view that wou开发者_StackOverflow社区ld have the following cols:

computer_id

name

ip_address

user

I am struggling with how to do this. I am sure it cannot be that complicated but having difficulty trying to reference the same columns (key, value) multiple times.

Thanks in advance.

You can group by the computer id, and pick the right keys using max and case. For example:

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

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

An other solution is to 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

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

What you're querying is a table that follows the EAV model design. Here's an article with some examples of how EAVs can/need to be queried.

Here's a subquery sample for your table definition:

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