Want to fetch the data in the form of a table

Question

I have written this query:

SELECT
    d.DetailId, 
    i.ItemId,
    d.fieldId,
    d.Fieldvalue,
    f.Name 
FROM
   EntityItemDetails d 
   inner join EntityItems i 
      on i.ItemId = d.ItemId 
   inner join Fields f 
      on f.Id = d.FieldId
WHERE
   i.EntityId = 1

Output is:

DetailId ItemId  FieldId   FieldValue     FieldName
1       1        9         Defect1        Name
2       1        10        abcdef         Description
5       1        11        testing123     Status

I want result in this way:

  Name           Description       Status
  TestField      abcdef            testing123

Please suggest how to get this result by writing a query in sql.

Where does the value `TestField` for your `Name` column come from ?? — marc_s, Aug 23 '11 at 11:53
This is one reason why you should not use EAV tables except in very rare cases. — HLGEM, Aug 23 '11 at 17:19

score 4 · Answer 1 · answered Aug 23 '11 at 16:29

What you want is called pivoting, and starting from SQL Server 2005, you can use a standard syntax for pivoting in Transact-SQL, with the help of the PIVOT clause.

Your query could be transformed for use with PIVOT like this:

WITH source AS (
  /* this CTE is actually your original query */
  SELECT
    EntityItemDetails.DetailId,
    EntityItems.ItemId,
    EntityItemDetails.fieldId,
    EntityItemDetails.Fieldvalue,
    Fields.Name
  FROM EntityItemDetails
    INNER JOIN EntityItems ON EntityItems.ItemId = EntityItemDetails.ItemId
    INNER JOIN Fields ON Fields.Id = EntityItemDetails.FieldId
  WHERE EntityItems.EntityId = 1
)

SELECT
  ItemId,
  Name,
  Description,
  Status
FROM (
  SELECT ItemId, FieldName, FieldValue
  FROM source
) s
PIVOT (
  MAX(FieldValue) FOR FieldName IN (
    Name,
    Description,
    Status
    /* add other possible names as necessary */
  )
) p

Chains · Answer 2 · 2011-08-23T16:36:00.197

2

SELECT
   FieldValue, 
   [Name], 
   [Description], 
   [Status]
FROM
   myTable -- OR (your posted query) myQuery
PIVOT
(
   MIN(FieldValue)
   FOR FieldName IN ([Name], [Description], [Status])
) AS myPivot;

edited Aug 23 '11 at 16:36

answered Aug 23 '11 at 16:25

Chains

12,541
8
45
62

score 0 · Answer 3 · answered Aug 24 '11 at 05:12

Tahnk u so much Andriy M.... with very few changes I could get the desired result. Chenges are as below

 WITH source AS ( 
  /* this CTE is actually your original query */  
    SELECT     EntityItemDetails.CreatedDate,     EntityItems.EntityItemId,
      EntityItemDetails.fieldId, 
         EntityItemDetails.Fieldvalue, 
              Fields.Name FieldName  FROM EntityItemDetails 
                INNER JOIN EntityItems ON EntityItems.EntityItemId =         EntityItemDetails.EntityItemId
                     INNER JOIN Fields ON Fields.Id = EntityItemDetails.FieldId     WHERE EntityItems.EntityId = 1 and (FieldId=9 or FieldId=10 or FieldId=11) ) 
                      SELECT   EntityItemId,   [Name],   [Description],   [Status] 
                      FROM (   SELECT EntityItemId, FieldName, FieldValue   FROM   source ) s 
                      PIVOT (   MAX(FieldValue) FOR FieldName IN (     [Name],       [Description],     [Status]  ) ) p

FOR FieldName IN ( Name, [Description], [Status] ) – Archana Aug 24 '11 at 05:46 — Archana, Aug 24 '11 at 05:46

Want to fetch the data in the form of a table

3 Answers3