5

I have the following table structure

AccountID   Property            Value
123456      Status              Active
123456      City                Los Angeles
123456      RegistrationDate    2018-05-11 11:30:14.000
543210      Status              Active
543210      City                Las Vegas
543210      RegistrationDate    2018-05-11 11:13:14.000
888888      Status              Inactive
888888      City                Toronto
888888      RegistrationDate    2015-05-12 11:13:14.000 

I want to be able to Select all rows where Property == Status and Value == Active And Property == RegistrationDate and Value == 2018-05-11 11:30:14.000

Im figuring I need to do some kind of GROUP BY and then select on that aggregation but I can't get my head around it(my sql is really rusty).

I want the following output for the above "query"

AccountID    Status    RegistrationDate
123456       Active    2018-05-11 11:30:14.000
543210       Active    2018-05-11 11:30:14.000

If all the columns had existed on the same row I would just write something like this:

SELECT AccountID FROM Property
WHERE Status = 'Active'
AND RegistrationDate = '2018-05-11 11:30:14.000'
10

The design pattern is called EAV (entity-attribute-value). It's usual to need either GROUP BY or queries with multiple joins with this design:

SELECT
      a.AccountID               
FROM 
      tbl AS a
      JOIN tbl AS b
      ON a.AccountID = b.AccountID
WHERE
      a.Property = 'Status' 
  AND a.Value    = 'Active'

  AND b.Property = 'RegistrationDate' 
  AND b.Value    = '2018-05-11 11:30:14.000' ;
6

Another way to express this is to use PIVOT to convert the EAV structure to tabular form. Given:

DROP TABLE IF EXISTS dbo.EAV;

CREATE TABLE dbo.EAV
(
    AccountID integer NOT NULL,
    Property varchar(30) NOT NULL,
    Value sql_variant NULL
);

INSERT dbo.EAV
    (AccountID, Property, [Value])
VALUES
    (123456, 'Status', CONVERT(sql_variant, 'Active')),
    (123456, 'City', CONVERT(sql_variant, 'Los Angeles')),
    (123456, 'RegistrationDate', CONVERT(sql_variant, CONVERT(datetime, '2018-05-11 11:30:14.000', 121))),
    (543210, 'Status', CONVERT(sql_variant, 'Active')),
    (543210, 'City', CONVERT(sql_variant, 'Las Vegas')),
    (543210, 'RegistrationDate', CONVERT(sql_variant, CONVERT(datetime, '2018-05-11 11:13:14.000', 121))),
    (888888, 'Status', CONVERT(sql_variant, 'Inactive')),
    (888888, 'City', CONVERT(sql_variant, 'Toronto')),
    (888888, 'RegistrationDate', CONVERT(sql_variant, CONVERT(datetime, '2015-05-12 11:13:14.000', 121)));

An example query is:

SELECT
    P.AccountID,
    P.[Status],
    P.City,
    P.RegistrationDate
FROM dbo.EAV AS E
PIVOT (MAX([Value]) 
    FOR Property IN 
    (
        [Status], 
        [City], 
        [RegistrationDate])
    ) AS P
WHERE 
    P.[Status] = CONVERT(sql_variant, 'Active')
    AND P.RegistrationDate = CONVERT(sql_variant, CONVERT(datetime, '2018-05-11 11:30:14.000', 121));

Result:

AccountID | Status | City        | RegistrationDate   
--------: | :----- | :---------- | :------------------
   123456 | Active | Los Angeles | 11/05/2018 11:30:14

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.