Reputation: 1029
I have a particular table which has the following columns -
The Attribute ID can have 6 possible values from 1-6. This table will contain entries for all customers. Each row for a customer contains entry for 1-6 values.
Now What I would want to achieve is as follows -
I execute the following query -
SELECT CUST_ID,
CASE WHEN "ATTR ID"=1 THEN "ATTR_VALUE" END AS ATTR_ID_1,
CASE WHEN "ATTR ID"=2 THEN "ATTR_VALUE" END AS ATTR_ID_2,
CASE WHEN "ATTR ID"=3 THEN "ATTR_VALUE" END AS ATTR_ID_3,
CASE WHEN "ATTR ID"=4 THEN "ATTR_VALUE" END AS ATTR_ID_4,
CASE WHEN "ATTR ID"=6 THEN "ATTR_VALUE" END AS ATTR_ID_5,
CASE WHEN "ATTR ID"=7 THEN "ATTR_VALUE" END AS ATTR_ID_6
FROM "TABLE_NAME" where CUST_ID='20002123'
This produces the 6 columns fine but it ends up creating 3 rows as well -
Should I pivot the table ?
Thanks
Upvotes: 0
Views: 68
Reputation: 17943
You can aggregate using GROUP BY CUST_ID
to get the multiple rows as single row.
Try changing your query like following.
SELECT CUST_ID,
Max(ATTR_ID_1) AS ATTR_ID_1,
Max(ATTR_ID_2) AS ATTR_ID_2,
Max(ATTR_ID_3) AS ATTR_ID_3,
Max(ATTR_ID_4) AS ATTR_ID_4,
Max(ATTR_ID_5) AS ATTR_ID_5,
Max(ATTR_ID_6) AS ATTR_ID_6
FROM (SELECT CUST_ID,
CASE WHEN "ATTR ID" = 1 THEN "ATTR_VALUE" END AS ATTR_ID_1,
CASE WHEN "ATTR ID" = 2 THEN "ATTR_VALUE" END AS ATTR_ID_2,
CASE WHEN "ATTR ID" = 3 THEN "ATTR_VALUE" END AS ATTR_ID_3,
CASE WHEN "ATTR ID" = 4 THEN "ATTR_VALUE" END AS ATTR_ID_4,
CASE WHEN "ATTR ID" = 6 THEN "ATTR_VALUE" END AS ATTR_ID_5,
CASE WHEN "ATTR ID" = 7 THEN "ATTR_VALUE" END AS ATTR_ID_6
FROM "TABLE_NAME"
WHERE CUST_ID = '20002123')T
GROUP BY CUST_ID
Upvotes: 1
Reputation: 6528
You are quite close:
SELECT CUST_ID,
max(CASE WHEN "ATTR ID"=1 THEN "ATTR_VALUE" END) AS ATTR_ID_1,
max(CASE WHEN "ATTR ID"=2 THEN "ATTR_VALUE" END) AS ATTR_ID_2,
max(CASE WHEN "ATTR ID"=3 THEN "ATTR_VALUE" END) AS ATTR_ID_3,
max(CASE WHEN "ATTR ID"=4 THEN "ATTR_VALUE" END) AS ATTR_ID_4,
max(CASE WHEN "ATTR ID"=6 THEN "ATTR_VALUE" END) AS ATTR_ID_5,
max(CASE WHEN "ATTR ID"=7 THEN "ATTR_VALUE" END) AS ATTR_ID_6
FROM "TABLE_NAME" where CUST_ID='20002123'
Group by cust_id;
Also, you can look at pivot.
Upvotes: 1