0

Could you please make some comments on the following strange and complicated design. I want to continue with this design. It is an existing design and not created by me.

Employee

EmpID
-----
  1
  2

Attribute

AttributeID Name Visible
------------------------
  1        Name    1
  2        Age     1
  3        Salary  1

EmployeeAttribute

EmpID AttributeID Value
-----------------------
 1       1        Rauf
 1       2        23
 1       3        100000 
 2       1        Amal
 2       3        50000

I want to select the above table as follows 

EmpID   Name  Age  Salary 
-------------------------
  1     Rauf   23   100000 
  2     Amal         50000

How can I do it in SQL Server 2008 ?

Rauf
  • 12,326
  • 20
  • 77
  • 126

2 Answers2

1

This is an EAV model. To get the desired results you would need to PIVOT on AttributeID either using PIVOT or as below.

SELECT EmpID,
       MAX(CASE WHEN AttributeID =1 THEN Value END) AS Name,
       MAX(CASE WHEN AttributeID =2 THEN Value END) AS Age,
       MAX(CASE WHEN AttributeID =3 THEN Value END) AS Salary
FROM EmployeeAttribute
GROUP BY EmpID

EAV is flexible but has a lot of disadvantages (google "EAV anti pattern" to find out more on this.)

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Could you please use query with table names. See my EDIT. – Rauf Nov 14 '11 at 12:41
  • @Rauf - Just replace `T` with `EmployeeAttribute`. You don't need the other tables for your desired results. – Martin Smith Nov 14 '11 at 12:41
  • `SELECT * FROM EmployeeAttributes PIVOT ( MAX(VALUE) FOR AttributeID IN (1,2,3) ) ORDER EmployeeID` gives error . Error **Incorrect syntax near '1'.**Why ? – Rauf Nov 14 '11 at 13:20
  • 1
    @Rauf - You would need `SELECT * FROM EmployeeAttributes PIVOT ( MAX(VALUE) FOR AttributeID IN ([1],[2],[3]) ) P ORDER BY EmployeeID` – Martin Smith Nov 14 '11 at 13:30
  • Well It works. But I can not use like this `SELECT * FROM EmployeeAttributes PIVOT ( MAX(VALUE) FOR AttributeID IN (SELECT [AttributeID] FROM [Attributes]) ) P`. Why :( ? – Rauf Nov 14 '11 at 13:52
  • @Rauf - That simply isn't supported. If you need a dynamic pivot you would need dynamic SQL (googling "dynamic pivot" should bring back some example code) – Martin Smith Nov 14 '11 at 13:54
  • http://www.sqlprof.com/blogs/sqldev/archive/2008/04/12/pivots-with-dynamic-columns-in-sql-server-2005-2008.aspx – Rauf Nov 14 '11 at 14:00
  • http://stackoverflow.com/questions/8122862/change-column-name-while-using-pivot-sql-server-2008 – Rauf Nov 14 '11 at 14:21
1

You can self join too

SELECT
   T1.EmpID,
   T1.Value As [name], T2.Value As [age], T3.Value As [salary]
FROM
   EmployeeAttribute T1
   JOIN
   EmployeeAttribute T2 ON T1.EmpID= T2.EmpID
   JOIN
   EmployeeAttribute T3 ON T1.EmpID= T3.EmpID
WHERE
   T1.AttributeID =1
   AND
   T2.AttributeID =2
   AND
   T3.AttributeID =3
gbn
  • 422,506
  • 82
  • 585
  • 676