How to create separate columns for values in Database

Question

I have an SQL query that displays information from different tables in a database. One of the fields is called PieceType and this contains values like PLT, CASE, CTN etc. Each company could have a different number of PieceTypes e.g. company 4 could have just PLT, but company 5 could have 10 different types. I want to display these types in separate columns like:

Plt | CASE | CTN

My SQL query:

SELECT  c.Name,
        jp.PieceType
FROM customer c
LEFT JOIN job_new jn ON ja.JobID = jn.ID
LEFT JOIN job_pieces jp ON ja.JobID = jp.ID
WHERE c.Company_ID = compid
GROUP BY c.ID

Right now the query just displays one value from PieceTypes even though the company might have multiple piece types. I tried GROUP_CONCAT(DISTINCT jp.PieceType) but that displays all the values in the same column. I need each piece to be in a separate column. Sample Database can be found on sqlfiddle: http://www.sqlfiddle.com/#!9/c34306/3

I don't really understand why you want different columns, you can instead use case statements for each of the type you have assuming you have limited set . select c.name, case when jp.PieceType='Plt' then 'Plt' end as Plt, case when jp.PieceType='CASE' then 'CASE' end as 'CASE' from ... — Bhargav Sarvepalli, Jul 07 '15 at 11:25
@BhargavSarvepalli It will help displaying the report I need if they are separate because using one column wasn't displaying them right. I can't create a case for each piece, like I said one company could only have one but another could have 30 — user123456789, Jul 07 '15 at 11:28
If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Jul 07 '15 at 12:08
Is there a way to just split the values? Like I said if I use `GROUP_CONCAT(DISTINCT jp.PieceType)` the values appear as CTN,FP,CASE. Would it be possible to split each value into a separate column? — user123456789, Jul 07 '15 at 13:37

score 0 · Answer 1 · answered Jul 07 '15 at 11:31

0

What you could do is a select where you do a lookup of the property you want.

ie:

Select 
name,
(select property from job_pieces where companyid = id and property = 'A') as A,
(select property from job_pieces where companyid = id and property = 'B') as B,
(select property from job_pieces where companyid = id and property = 'C') as C
 from company

answered Jul 07 '15 at 11:31

Jonas

185
12

But would property need to be each piece type? A company could have 30 piece types so I couldn't to a select for each one – user123456789 Jul 07 '15 at 11:33
If you have 30 different types in total, you'd have to do 30 loopups. – Jonas Jul 07 '15 at 11:34
You can probably do it rather quickly with search and replace with some copy + past magic – Jonas Jul 07 '15 at 11:35
Do you really need them in different columns at this layer? A more standard way to do it would be to get all the properties and the loop through them at the presentation layer. – Jonas Jul 07 '15 at 11:37
Yes it would be easier to get them in separate columns because the code to display the report is already done. I was just hoping to edit this query – user123456789 Jul 07 '15 at 11:42

Pholochtairze · Answer 2 · 2015-07-07T14:46:47.443

0

I think you are looking for PIVOT TABLE. Hope this works :

SET group_concat_max_len=4294967294;
SET @COLUMNS = NULL;

/* Build columns to pivot */

SELECT GROUP_CONCAT(
        DISTINCT CONCAT(
            'CASE WHEN jp.PieceType = "', 
            jp.PieceType ,
            '" THEN 1 ELSE NULL END AS ',
            jp.PieceType
        )
) INTO @COLUMNS
FROM job_pieces jp;

/* Build full query */
SET @SQL = CONCAT(
        'SELECT  
                c.Name,
                ',@COLUMNS,'
        FROM customer c
        LEFT JOIN job_new jn ON ja.JobID = jn.ID
        LEFT JOIN job_pieces jp ON ja.JobID = jp.ID
        WHERE c.Company_ID = compid
        GROUP BY c.ID'
);

/* Prepare and execute the query*/
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

edited Jul 07 '15 at 14:46

answered Jul 07 '15 at 11:34

Pholochtairze

1,836
1
14
18

I get an SQL error for the PREPARE statement. Is that really needed? – user123456789 Jul 07 '15 at 11:41
Sorry forgot `INTO @COLUMNS` in the query. Hope this works now. What do you mean by "Is that really needed ?" – Pholochtairze Jul 07 '15 at 11:47
I'm getting the error: Result consisted of more than one row – user123456789 Jul 07 '15 at 11:55
Forgot `GROUP_CONCAT` before `DISTINCT CONTAT` . Hope this is the good try :) Sorry for those mistakes it's hard to see a missing element without running the query live. – Pholochtairze Jul 07 '15 at 12:03
ok thanks that got rid of the error but the result it displays is 'PRL', '9', NULL, NULL, '0', '0', '2684.18', '1', 'CTN,FP', 'GROUP_CONCAT(IF(jp.PieceType = \"CTN\", 1, NULL)) AS CTN,GROUP_CONCAT(IF(jp.PieceType = \"FP\", 1, NULL)) AS FP' – user123456789 Jul 07 '15 at 13:21
I want it to just display the PieceType. It is getting the PieceTypes, you can see CTN and FP which is correct but it is not displaying them in separate columns – user123456789 Jul 07 '15 at 13:22
I've changed the CONCAT part with a CASE WHEN, if it did not work, maybe could you consider making a [sqlfiddle](http://www.sqlfiddle.com) with fake data so that I can try on this ;) – Pholochtairze Jul 07 '15 at 14:02
Adding the CASE WHEN causes this error: 1 row(s) affected, 1 warning(s): 1260 Row 16 was cut by GROUP_CONCAT(). I will look into sqlfiddle, I have never used it before – user123456789 Jul 07 '15 at 14:21
There was an open parenthesis with no closing one. Now should be better I think. – Pholochtairze Jul 07 '15 at 14:29
After looking at the error, I presume it comes from the fact the group_concat has a greater length than what it's allowed to. So I added the line `SET group_concat_max_len=4294967294;` to prevent it. Let's pray it was the problem ! – Pholochtairze Jul 07 '15 at 14:49
that fixed the error but the result is displaying a line of code like before. Will the database I created in sqlfiddle help? – user123456789 Jul 07 '15 at 14:55
Yeah it will help because if people use the link (edit your question so that everyone sees it) then people can run their query on it, so I can test and see the bugs much more quickly. – Pholochtairze Jul 07 '15 at 14:57
any luck figuring it out? – user123456789 Jul 07 '15 at 15:25
Working on it. The thing is that I thought you had several rows with different jp.PieceType, but you have one row with comma-separated PieceTypes and I'm having trouble with this. When I figure that out I give you my answer. – Pholochtairze Jul 07 '15 at 15:34
sorry I didn't explain everything well. I appreciate the help thank you – user123456789 Jul 07 '15 at 15:37
I don't think Pivot table is the correct solution for what I am trying to do. This question is like what I need http://stackoverflow.com/questions/5829543/how-to-parse-a-string-and-create-several-columns-from-it. But I don't know how to edit it for my query – user123456789 Jul 08 '15 at 11:13

How to create separate columns for values in Database

2 Answers2