How to create a view from table row values

Question

I have a table like this:

ID  |  staff_id  |  Meta_key  | Meta_Value  | Meta_Group
1          1         landline      456          contact
2          1          mobile       777          contact
3          1          email      [email protected]          contact
4          2         landline      453          contact
5          2          mobile       888          contact
6          2          email      [email protected]          contact

I want to create a view called view_contact_details that looks like this:

 staff_id  |  landline  |  mobile  | email
    1          456          777      [email protected]
    2          453          888      [email protected]

Is this possible to do? I appreciate any help and thank you in advance.

Possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Tab Alleman, Jun 16 '16 at 13:37

score 1 · Accepted Answer · answered Jun 16 '16 at 13:46

You can do this with a CASE wrapped in a MAX

Sample Data

CREATE TABLE #TestData (ID int, staff_id int, meta_key varchar(10), meta_value varchar(10), meta_group varchar(10))
INSERT INTO #TestData (ID, staff_id, meta_key, meta_value, meta_group)
VALUES
(1,1,'landline','456','contact')
,(2,1,'mobile','777','contact')
,(3,1,'email','[email protected]','contact')
,(4,2,'landline','453','contact')
,(5,2,'mobile','888','contact')
,(6,2,'email','[email protected]','contact')

Query;

CREATE VIEW view_contact_details
AS
SELECT
Staff_ID
,MAX(CASE WHEN Meta_Key = 'landline' THEN Meta_Value END) Landline
,MAX(CASE WHEN Meta_Key = 'mobile' THEN Meta_Value END) Mobile
,MAX(CASE WHEN Meta_Key = 'email' THEN Meta_Value END) Email
FROM #TestData
GROUP BY staff_id

Results;

Staff_ID    Landline    Mobile  Email
1           456         777     [email protected]
2           453         888     [email protected]

How to create a view from table row values

1 Answers1