CODEWITHSUNDEEP
mysqlsqldynamic-data
gandil
gandil

Reputation: 5418

SQL with dynamic fields

I have 4 tables:

CREATE TABLE Category (
    Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL,
) Engine=InnoDB;

CREATE TABLE Category_Template (
    Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL,
    Category_Id INT UNSIGNED NOT NULL,
    FOREIGN KEY (Category_Id) REFERENCES Category (Id)
) Engine=InnoDB;

CREATE TABLE Post (
    Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Post_Name VARCHAR(255) NOT NULL,
    Category_Id INT UNSIGNED NOT NULL,
    FOREIGN KEY (Category_Id) REFERENCES Category (Id)
) Engine=InnoDB;

CREATE TABLE Post_Fields (
    Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Post_Id INT UNSIGNED NOT NULL,
    Category_Template_Id INT UNSIGNED NOT NULL,
    Value VARCHAR(255) NOT NULL,
    FOREIGN KEY (Category_Template_Id) REFERENCES Category_Template (Id)
) Engine=InnoDB;

As an example, assume there's a post belonging to the travel category:

INSERT INTO Category 
(Id, Name) 
  VALUES
(1, 'Where to go?')
;

INSERT INTO Category_Template
(Category_Template_Id, Name,       Category_Id)
  VALUES
(1,                    'Budget',   1),
(2,                    'Location', 1),
(3,                    'Time',     1)
;

INSERT INTO Post
(PostId, Post_Name,        Category_Id)
  VALUES
(1,     'America is good', 1),
(2,     'Hot Travel',      1)
;

INSERT INTO Post_Fields 
(Id, Post_Id, Category_Template_Id, Value)
  VALUES
(1,  1,       1,                    '1000 $'),
(1,  1,       2,                    'New York'),
(1,  1,       3,                    'January 2012'),
(1,  2,       1,                    '2000 $'),
(1,  2,       2,                    'Brasil'),
(1,  2,       3,                    'May 2012')
;

I want to run an SQL query which will list all posts that belong to a given category and list all fields for those posts as separate columns, with the category template names as column names. The results should be something like:

Post Id     Post_Name         Budget         Location        Time
1           America is good   1000 $         New York        January 2012
2           Hot Travel        2000 $         Brasil          May 2012

My problem is that category templates for different categories have different names, making it hard to write a single general use query. For example, a category for traveling can have 3 templates (location, budget, time) whereas a category for books have 4 templates (abstract, content, author_bio, summary). How can I write a statement that turns one column into multiple columns (based on the value in the same row but in another column), when the exact number of output columns will vary? Similarly, how can I write a statement that turns one column into multiple column headers, considering that the number of output columns will vary?

Upvotes: 1

Views: 175

Answers (1)

dar7yl
dar7yl

Reputation: 3747

SELECT Post_Id as `Post Id`, Post_Name,
    (SELECT Value FROM Post_Fields WHERE Category_Template_Id=1 
          AND Post_Fields.Post_Id=Post.Post_id) AS `Budget`,
    ... more of the same ...
FROM Post WHERE Post_Id = 1

Upvotes: 1

Related Questions