Query regarding Column and row merge

Database

Query regarding Column and row merge

 
5 REPLIES

Re: Query regarding Column and row merge

Hi,

I need assistance to create the below table using Tables 1 and 2.

Assume Table1 contains - Customernbr, Customername,  Attribute1, Attirbute2 and Attribute3. Consider the attributes to have info about the age, income, ethnicity of the customer. Data is unique at a customernbr customername level. (Same customer number will have 2 names in some cases). Also note age could be present across any of the attributes and similarly for income and ethnicity. 

Cnbr   Cname    Att1     Att2     Att3
100 AA 1000 24 NULL
100 AAA 1200 23 NULL
102 BB Asian 1222 44
103 CCC 34 1112 NULL

Cnbr   Att1       Att2      Att3
100 Income Age NULL
101 Income Age NULL
102 Ethnicity Income Age
103 Age Income NULL

We need the table 3 which is as follows - Table2 - This is at a customernbr level. We will have same customernbr as in table1 and attribute1, 2 and 3. We will show for each customer number what is the info present in attribute 1 and so on as show below

Cnbr   Cname  Income  Age   Ethnicity
100 AA 1000 24 NULL
100 AAA 1200 23 NULL
102 BB 1222 44 Asian
103 CCC 1112 34 NULL

Thanks in advance.Tried my best to explain. Let me know if you need more details.

N/A

Re: Query regarding Column and row merge

Wow, that's a sick data model, hopefully you need this query to fix this mess.

SELECT
t1.Cnbr
,CASE
WHEN t2.Att1 = 'Income' THEN t1.Att1
WHEN t2.Att2 = 'Income' THEN t1.Att2
WHEN t2.Att3 = 'Income' THEN t1.Att3
END AS Income
,CASE
WHEN t2.Att1 = 'Age' THEN t1.Att1
WHEN t2.Att2 = 'Age' THEN t1.Att2
WHEN t2.Att3 = 'Age' THEN t1.Att3
END AS Age
,CASE
WHEN t2.Att1 = 'Ethnicity' THEN t1.Att1
WHEN t2.Att2 = 'Ethnicity' THEN t1.Att2
WHEN t2.Att3 = 'Ethnicity' THEN t1.Att3
END AS Ethnicity
FROM t1 JOIN t2 ON t1.Cnbr = t2.Cnbr

Re: Query regarding Column and row merge

Thanks Dieter for the quick response :)

Just one more clarification(confirming it) - Shouldn't the select statement have the cname as well from T1 ? 

N/A

Re: Query regarding Column and row merge

Of course, simply add it, when you need it.

Re: Query regarding Column and row merge

Thanks ! That does solve my problem