SQL Help - How to "linearize" data?

Question

I'm not sure if "linearization" is the proper term, but I need a query that will output something like this:

item_name    item_price  first_name  last_name
-----------------------------------------------
'camera'    '100'        'Little'    'Timmy'
'computer'  '200'        'Little'    'Timmy'

Here's my DB: http://pastebin.com/iS4QKHEb

Any help is greatly appreciated. Thanks.

Answer 1

Can you explain what you're looking for? An SQL query to just display all data? Something like

SELECT *

or

SELECT item_name, item_price, first_name, last_name

?

Answer 2

Here's:

SELECT MAX(CASE WHEN f.name = 'item_name' THEN fd.value ELSE NULL END) AS item_name,
       MAX(CASE WHEN f.name = 'item_price' THEN fd.value ELSE NULL END) AS item_price,
       MAX(CASE WHEN f.name = 'first_name' THEN fd.value ELSE NULL END) AS first_name,
       MAX(CASE WHEN f.name = 'last_name' THEN fd.value ELSE NULL END) AS last_name
  FROM FIELDDATA fd
  JOIN FIELD f ON f.id = fd.fieldid

But the problem is I don't see what there is to group by in order to get the two rows you want as output - the datasourceid is the same for all the records, so it can't be used.

And if you [likely] want this to be dynamic, it'll require dynamic SQL via MySQL Prepared Statement syntax.

Answer 3

Unfortunately - if you've given us everything you have... "you can't get there from here".

If you want to get...

item_name    item_price  first_name  last_name
-----------------------------------------------
'camera'    '100'        'Little'    'Timmy'
'computer'  '200'        'Little'    'Timmy'

... from ...

INSERT INTO `fielddata` (`Id`, `DataSourceId`, `FieldId`, `Value`) VALUES
(1, 15, 100, 'camera'),
(2, 15, 101, '100'),
(3, 15, 100, 'computer'),
(4, 15, 101, '200'),
(5, 15, 102, 'Little'),
(6, 15, 103, 'Timmy');


INSERT INTO `field` (`Id`, `FieldAttributeId`, `FieldSetId`, `FormId`, `LookupFieldTypeId`, `Description`, `DisplayOrder`, `IsDeleted`, `Label`, `Name`) VALUES
(102, 0, 0, 1, 6, 'First Name Description', 0, 0, 'First Name Label', 'first_name'),
(103, 0, 0, 1, 6, 'Last Name Description', 0, 0, 'Last Name Label', 'last_name'),
(100, 0, 1, 1, 6, 'Item Name Description', 0, 0, 'Item Name Label', 'item_name'),
(101, 0, 1, 1, 6, 'Item Price Description', 0, 0, 'Item Price Label', 'item_price');

You'll need to be able to pull each column as a sub-select like so...

select fd.value, 
  from fielddata fd
 inner join field f on (fd.FieldId = f.Id)
 where f.name = 'item_name'

Problem is, once you get those put together, you don't have a column to "glue" the recordsets back together - eg you won't know which price goes with which name because there's not a item_id of sorts on the fielddata table.

Let us know if there's something that accidentally got left out.

Just out of curiosity - is this class work? Looks like an exercise of sorts...

Good luck!