Sébastien Sébastien - 6 months ago 7
SQL Question

How to write the query?

I have one table that contains customers (The goal of this table was to be able to add fields without DB-Update). The table looks like this:

CustId Property PropertyValue

1 Name Smith
1 Email smith@gmail.com
2 Name Donalds
2 Email donalds@gmail.com
3 Name john


(The customer 3 has no entry for "Email" in the table)

Expected result: I want to get one line per client (Mail) and if the customer has no email, display still one line with NULL.

CustId Property PropertyValue

1 Email smith@gmail.com
2 Email donalds@gmail.com
3 Email NULL


Has someone the solution ?

Answer
DECLARE @t TABLE (
    CustId INT,
    Property VARCHAR(50),
    PropertyValue VARCHAR(50)
)
INSERT INTO @t (CustId, Property, PropertyValue)
VALUES
    (1, 'Name', 'Smith'),
    (1, 'Email', 'smith@gmail.com'),
    (2, 'Name', 'Donalds'),
    (2, 'Email', 'donalds@gmail.com'),
    (3, 'Name', 'john')

SELECT CustId
     , Name = 'Email'
     , Value = MAX(CASE WHEN Property = 'Email' THEN PropertyValue END)
FROM @t
GROUP BY CustId