1

I have a table containing data as shown below enter image description here

I have created this query to return the result

SELECT id,customer_name,job_number, 
IF(designer_name='des1',total_hrs, "") as des1,
IF(designer_name='des2',total_hrs, "") as des2,
IF(designer_name='des3',total_hrs, "") as des3,
IF(designer_name='des4',total_hrs, "") as des4 

FROM practice.test;

enter image description here

If there are 50 designers that would mean I will have to write the If statement multiple times which would not be good.

I want to know if there is a better way to get the result?

Table Code:

--
-- Database: `practice`
--

-- --------------------------------------------------------

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `designer_id` int(11) NOT NULL,
  `designer_name` varchar(45) NOT NULL,
  `customer_name` varchar(45) NOT NULL,
  `job_number` varchar(45) NOT NULL,
  `total_hrs` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`id`, `designer_id`, `designer_name`, `customer_name`, `job_number`, `total_hrs`) VALUES
(1, 1, 'des1', 'aa', 'j101', '11'),
(2, 1, 'des1', 'bb', 'j102', '8'),
(3, 2, 'des2', 'cc', 'j103', '2'),
(4, 2, 'des2', 'dd', 'j104', '2'),
(5, 2, 'des2', 'ee', 'j105', '5'),
(6, 3, 'des3', 'ff', 'j106', '13'),
(7, 3, 'des3', 'gg', 'j107', '32'),
(8, 3, 'des3', 'ii', 'j108', '4'),
(9, 3, 'des3', 'kk', 'j109', '3'),
(10, 3, 'des3', 'll', 'j110', '4'),
(11, 4, 'des4', 'mm', 'j111', '1'),
(12, 4, 'des4', 'nn', 'j112', '6');
jaco0646
  • 15,303
  • 7
  • 59
  • 83
user3289968
  • 97
  • 2
  • 12
  • 2
    It is a Pivot table problem; Consider handling Data display related requirements in your application code (eg: PHP, C++ etc). – Madhur Bhaiya Dec 14 '18 at 14:04

2 Answers2

0

What is that you're trying to achieve? If it's the total of hours try with 

SELECT designer_name, sum(total_hrs) FROM test GROUP BY designer_id
rodelarivera
  • 751
  • 5
  • 14
  • I am trying to generate a pivot table using the query I mentioned in the post. The problem is the if statement IF(designer_name='des1',total_hrs, "") as des1, I have the write the designer_name='desX' for each value for designer_name. I want to know if there is any better way to achieve the result. If the number of the designers increase I will have to manually edit the query and add in the new line for the new designer. – user3289968 Dec 14 '18 at 18:18
  • Do the query that I wrote gave you the information you're looking for? If so, please don't forget to mark any response as accepted. – rodelarivera Dec 15 '18 at 19:56
0

It's have no sense. I don't think what you really need this. What the problem to do simple request like

SELECT id, customer_name, job_number, designer_name, total_hrs
FROM practice.test

Can you explain, why you need result in this way. Probably, you have wrong logic in code, what will handle result.

Sergey S
  • 68
  • 8