CODEWITHSUNDEEP
sqlsql-server
Sébastien
Sébastien

Reputation: 79

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        [email protected]  
2          Name         Donalds  
2          Email        [email protected]  
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        [email protected]  
2          Email        [email protected]  
3          Email        NULL

Has someone the solution ?

Upvotes: 3

Views: 106

Answers (5)

PriVictor
PriVictor

Reputation: 49

SELECT CustId
     , MIN(CASE WHEN Property IS NULL THEN 'Email' ELSE Property END) Property
     , MIN(PropertyValue) PropertyValue
FROM TableName
GROUP BY CustId
HAVING Property = 'Email';

Upvotes: 1

sagi
sagi

Reputation: 40471

You can do it using a derived table containing all possible ID's , and then left joining only to the Emails on the original table:

SELECT t.custID,'EMAIL',s.PropertyValue
FROM(SELECT DISTINCT custID
     FROM YourTable) t
LEFT OUTER JOIN YourTable s
 ON(t.custID = s.custID and s.property = 'Email')

Can also be done with a correlated query:

SELECT DISTINCT t.CustID,'EMAIL',
       (SELECT s.PropertyValue
        FROM YourTable s
        WHERE s.custID = t.custID and s.Property = 'Email')
FROM YourTable t

Upvotes: 3

Devart
Devart

Reputation: 121902

DECLARE @t TABLE (
    CustId INT,
    Property VARCHAR(50),
    PropertyValue VARCHAR(50)
)
INSERT INTO @t (CustId, Property, PropertyValue)
VALUES
    (1, 'Name', 'Smith'),
    (1, 'Email', '[email protected]'),
    (2, 'Name', 'Donalds'),
    (2, 'Email', '[email protected]'),
    (3, 'Name', 'john')

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

Upvotes: 4

gofr1
gofr1

Reputation: 15977

Self join with same table, property passed via variable

DECLARE @prop nvarchar(max) = 'Email'

SELECT DISTINCT c.CustId, @prop as Property, c1.PropertyValue
FROM yourtable c 
LEFT JOIN yourtable c1
    ON c.CustId = c1.CustId and c1.Property = @prop

Output will be as you posted in your question.

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

Query 1

Select t1.CustId 
     , ISNULL(t2.Property ,'Email') AS Property 
     , t2.PropertyValue
FROM TableName t1 
LEFT JOIN TableName t2 ON t1.CustId = t2.CustId
                      AND t2.Property = 'Email'
WHERE t1.Property = 'Name'

Result Set 1

╔════════╦══════════╦═══════════════════╗
║ CustId ║ Property ║   PropertyValue   ║
╠════════╬══════════╬═══════════════════╣
║      1 ║ Email    ║ [email protected]   ║
║      2 ║ Email    ║ [email protected] ║
║      3 ║ Email    ║ NULL              ║
╚════════╩══════════╩═══════════════════╝

Query 2

Another query for a more readable result set should look something like....

Select t1.CustId 
     , t1.PropertyValue [CustomerName]
     , t2.PropertyValue [CustomerEmail]
FROM TableName t1 
LEFT JOIN TableName t2 ON t1.CustId = t2.CustId
                      AND t2.Property = 'Email'
WHERE t1.Property = 'Name'

Result Set 2

╔════════╦══════════════╦═══════════════════╗
║ CustId ║ CustomerName ║   CustomerEmail   ║
╠════════╬══════════════╬═══════════════════╣
║      1 ║ Smith        ║ [email protected]   ║
║      2 ║ Donalds      ║ [email protected] ║
║      3 ║ john         ║ NULL              ║
╚════════╩══════════════╩═══════════════════╝

Upvotes: 5

Related Questions