Creating a column from a string in mysql

Question

So what i'm trying to do left join this table that would define what a question is from the questions table and check with the responses table. I wrote the left join to define each question as its own column but its not working.. any ideas?

select *
FROM Leads l
/*JOIN Projects P ON L.projectid=P.projectid
JOIN Leads LE ON LE.LISTID=L.listid
JOIN Calls CA ON CA.LEADID=LE.leadid*/

join Lists li on (li.Listid=l.Listid)
JOIN Projects P ON (Li.projectid=P.projectid)
join Calls ca on (ca.leadid=l.leadid)

LEFT JOIN (
SELECT 
leadid,
Authorized=MIN(CASE qname WHEN  'Authorized' THEN RESPONSE END),
ReplacementName=MIN(CASE qname WHEN 'ReplacementName' THEN RESPONSE END),
SpokeToGroup=MIN(CASE qname WHEN 'SpokeToGroup' THEN RESPONSE END),
SpokeToName=MIN(CASE qname WHEN 'SpokeToName' THEN RESPONSE END),
SpokeToTitle=MIN(CASE qname WHEN 'SpokeToTitle' THEN RESPONSE END),
WishToRecieve=MIN(CASE qname WHEN 'WishToRecieve' THEN RESPONSE END),
Question01=MIN(CASE qname WHEN 'Question01' THEN RESPONSE END),
... -- lots of other fields
MAGExpiration=MIN(CASE qname WHEN 'MAGExpiration' THEN RESPONSE END),
SourceGroup=MIN(CASE qname WHEN 'SourceGroup' THEN RESPONSE END),
SourceID=MIN(CASE qname WHEN 'SourceID' THEN RESPONSE END),
ClientOtherID=MIN(CASE qname WHEN 'ClientOtherID' THEN RESPONSE END)

from Questions q join Responses r on (r.questionid=q.questionid)
)

If you have columns called something like "Foo1", Foo2", etc, this is usually a sign that your database is not correctly normalized. Questions should be in their own table. — Mark Byers
– Mark Byers, Commented Dec 21, 2012 at 18:25

Taryn · Accepted Answer · 2012-12-21 21:31:01Z

3

Your subquery missing a GROUP BY:

GROUP BY leadid

You are using all of the aggregate functions but you are not grouping by anything.

You should also include an ELSE on your CASE statements.

There might be better ways for you to perform this. If you are using SQL Server, then you can perform this much easier using the PIVOT function. Since you have so many fields that you are attempting to pivot, you should look at using dynamic SQL in sql server and prepared statements in MySQL.

Edit #1, you also appear to be missing a few key items:

select *
FROM Leads l
/*JOIN Projects P ON L.projectid=P.projectid
JOIN Leads LE ON LE.LISTID=L.listid
JOIN Calls CA ON CA.LEADID=LE.leadid*/

join Lists li on (li.Listid=l.Listid)
JOIN Projects P ON (Li.projectid=P.projectid)
join Calls ca on (ca.leadid=l.leadid)
LEFT JOIN 
(
  SELECT 
    leadid,
    MIN(CASE qname WHEN  'Authorized' THEN RESPONSE END) as Authorized,
    MIN(CASE qname WHEN 'ReplacementName' THEN RESPONSE END) as ReplacementName,
    MIN(CASE qname WHEN 'SpokeToGroup' THEN RESPONSE END) as SpokeToGroup,
    MIN(CASE qname WHEN 'SpokeToName' THEN RESPONSE END) as SpokeToName,
    MIN(CASE qname WHEN 'SpokeToTitle' THEN RESPONSE END) as SpokeToTitle,
    MIN(CASE qname WHEN 'WishToRecieve' THEN RESPONSE END) as WishToRecieve,
    MIN(CASE qname WHEN 'Question01' THEN RESPONSE END) as Question01,
    ... -- lots of other fields
    MIN(CASE qname WHEN 'MAGExpiration' THEN RESPONSE END) as MAGExpiration,
    MIN(CASE qname WHEN 'SourceGroup' THEN RESPONSE END) as SourceGroup,
    MIN(CASE qname WHEN 'SourceID' THEN RESPONSE END) as SourceID,
    MIN(CASE qname WHEN 'ClientOtherID' THEN RESPONSE END) as ClientOtherID
  from Questions q 
  join Responses r 
    on (r.questionid=q.questionid)
  group by leadid
) qr  -- an alias for the subquery
  on l.leadid = qr.leadid  -- the on for the join

edited Dec 21, 2012 at 21:31

answered Dec 21, 2012 at 18:25

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1922240 Over a year ago

Okay well that didn't help to much.. I'm running this in mysql on my MSSQL server the left join subquery runs fine is there any major changes from mssql to mysql?

Taryn Over a year ago

@user1922240 are you getting an error? Can you clarify what doesn't work?

user1922240 Over a year ago

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 419 and 419 is just the group by there is no ''

user1922240 Over a year ago

Okay now it's saying [Err] 1054 - Unknown column 'Authorized' in 'field list' the thing is i'm doing that query to define each row from the question's field as a column in my mssql server this works fine but let me check my table structure in my mssql server really fast.

Taryn Over a year ago

@user1922240 see my edit, change the way you are giving aliases to your columns in the subquery

|

Collectives™ on Stack Overflow

Creating a column from a string in mysql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related