Input

Here is create table MYSQL(I am not good at mssql)

CREATE TABLE Question(
    fruit varchar(10),
    season varchar(10),
    price numeric
);
INSERT INTO
    Question
VALUES
    ('apple', 'spring', 15),
    ('apple', 'summer', 20),
    ('apple', 'autumn', 35),
    ('apple', 'winter', 55),
    ('banana', 'spring', 13),
    ('banana', 'summer', 2),
    ('banana', 'autumn', 3),
    ('banana', 'winter', 5),
    ('peach', 'spring', 40),
    ('peach', 'summer', 50),
    ('peach', 'autumn', 33),
    ('peach', 'winter', 44);

Because my complete data has more than 300 kinds of fruit, so could not use apple, banana etc in sql query, also can't use spring, summer etc in sql query.

If there is any easy solution about this combine?

Output expect

CodePudding user response：

This is a basic way to do it using conditional aggregation, without needing to pivot:

select fruit, SUM(CASE WHEN season = 'spring' then price else 0 END) SpringPrice,
SUM(CASE WHEN season = 'autumn' then price else 0 END) AutumnPrice,
SUM(CASE WHEN season = 'winter' then price else 0 END) WinterPrice,
SUM(CASE WHEN season = 'summer' then price else 0 END) SummerPrice 
from Question
group by fruit

CodePudding user response：

Very easy to get a row per fruit, even if you don't know any of the fruit names up front:

SELECT * FROM dbo.Question
PIVOT (MAX(price) FOR season IN 
  ([spring],[summer],[autumn],[winter])
) AS p;

Much harder to get a column for season if you don't know them in advance (see some ideas here).

CodePudding user response：

use pivot as follows

SELECT *
FROM (
  SELECT 
    fruit
  , CONCAT(season, '_price') AS Col
  , price
  FROM Question
) Src
PIVOT (
  MAX(price) 
  FOR Col IN (
    [spring_price], [summer_price], 
    [autumn_price], [winter_price]
  ) 
) Pvt
ORDER BY fruit;

CodePudding user response：

The SQL language has a very strict requirement for you to know some things about the columns in the results at query compile time, before looking at any data. If you have to look at data to find out about the columns you want, you're stuck using (potentially dangerous) dynamic SQL, over three steps:

1. Run a query to find information about the desired columns.
2. Build a new SQL statement on the fly using the results from step 1.
3. Run the SQL from step 2.

In this case, we see spring, summer, autumn, and winter, which are fixed values that could avoid this issue. But we also see this:

can't use spring, summer etc in sql query.

That's a problem. It puts you back into dynamic SQL territory, because you're again looking into the data before the query is finished compiling.