CODEWITHSUNDEEP
sqlsql-server
Sayantan Mukherjee
Sayantan Mukherjee

Reputation: 205

Creating new columns in Sql Server based on other columns

I have a table T :

CREATE TABLE T 
(
id INT,
type VARCHAR(200),
type_value VARCHAR(10),
value  VARCHAR(200)
);


INSERT INTO T VALUES (1,    'HomePhone',   'p1',    '1234 ');
INSERT INTO T VALUES (1,    'HomePhone',    'p2',    '5678 ');
INSERT INTO T VALUES (1,    'HomePhone',    'p3',    '4567');
INSERT INTO T VALUES (1,    'WorkPhone',    'w1',    '9007 ');
INSERT INTO T VALUES (2,    'Email',             'e1',    '[email protected] ');
INSERT INTO T VALUES (2,    'Email',             'e1',    '[email protected]');
INSERT INTO T VALUES (2,    'Email',             'e2',    '[email protected]');
INSERT INTO T VALUES (3,    'WorkPhone',    'w1',    '0100');
INSERT INTO T VALUES (3,    'WorkPhone',    'w2',    '0110');
INSERT INTO T VALUES (4,    'OtherPhone',    'o1',    '1010 ');
INSERT INTO T VALUES (4,    'OtherPhone',    'o1',    '1110 ');
INSERT INTO T VALUES (4,    'OtherPhone',    'o1',    '1011');
INSERT INTO T VALUES (4,    'HomePhone',    'p1',    '2567 ');

I need to transform it into :

id   primaryhomephone    secondaryhomephone    primaryemail   secondaryemail   Primaryworkphone    secondaryworkphone    primaryotherphone   secondaryotherphone 
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1    1234                5678                  null           null             9007                 null                 null                null
2    null                null                  [email protected]    [email protected]      null                 null                 null                null
3    null                null                  null           null             0100                 0110                 null                null
4    2567                null                  null           null             null                 null                 1010                1011

Basically the field will be divided based on type_value. If there is two type_value for same id and type then first type will be primary and second type will be secondary.
For more than two type values for same id and type, discard the third one.
For more than two type values of same type(for example o1,o1) for id 4 first o1 will be primaryotherphone and second one will be secondaryprimaryphone.

Sorry if this question has been repeated before but somehow I can't solve it. Can anyone please help.
Thanks a lot

Upvotes: 0

Views: 78

Answers (3)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

You can use MAX/GROUP BY technique. PIVOT is quite complex for beginners, but I agree that the purpose of the PIVOT is the exactly what you need:

SELECT id
, MAX(CASE WHEN type_value = 'p1' THEN value END ) AS primaryhomephone
, MAX(CASE WHEN type_value = 'p2' THEN value END ) AS secondaryhomephone
, MAX(CASE WHEN type_value = 'p3' THEN value END ) AS thirdphone
, MAX(CASE WHEN type_value = 'w1' THEN value END ) AS workphone
, MAX(CASE WHEN type_value = 'w2' THEN value END ) AS secondaryworkphone
, MAX(CASE WHEN type_value = 'o2' THEN value END ) AS otherworkphone
, MAX(CASE WHEN type_value = 'e1' THEN value END ) AS primaryemail
, MAX(CASE WHEN type_value = 'e2' THEN value END ) AS secondaryemail
FROM T
GROUP BY id
;

Upvotes: 1

bjnr
bjnr

Reputation: 3437

You need to look after computed columns in SQL Server.

CREATE TABLE [dbo].T
(
   ...
   [primaryhomephone] AS (CASE WHEN type_value = 'p1' THEN value ELSE NULL END)
)

Alternatively you can use Views:

CREATE VIEW dbo.vwT
AS
SELECT 
   id
   ,(CASE WHEN type_value = 'p1' THEN value ELSE NULL END as [primaryhomephone]
   , (CASE WHEN type_value = 'p2' THEN value ELSE NULL END as [secondaryhomephone]
FROM dbo.T

Upvotes: 0

Sumit Gupta
Sumit Gupta

Reputation: 2192

You need to look into PIVOT Column presentation. You can use it in to ways, you can create new Table with your column and then use PIVOT Query to insert them there, or you can simply fetch at runtime. If there is lot of data and all other Programming interface are ready to upgrade then make new table, else just use query.

Here is the link for your reference: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Upvotes: 0

Related Questions