How to combine multiple rows to multiple col name with variable string in mssql?

Question

Input

fruit	season	price
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

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

fruit	spring_price	summer_price	autumn_price	winter_price
apple	15	20	35	55
banana	13	2	3	5
peach	40	50	33	44

also can't use spring, summer etc in sql query What does that mean? You have a situation calling for a pivot. Given the fixed set of columns (spring, summer, etc.) you could to this with conditional aggregation. — SMor
– SMor, Commented Mar 22, 2022 at 15:28
In all honesty, this calls for a solution outside of SQL. It's a thing that you'd normally do with a BI tool, such as Power BI or even Excel. You use "Fruit" and "Season" as Dimensions and then you use any aggregation (SUM. MAX) on the price. — Philippe
– Philippe, Commented Mar 22, 2022 at 15:38

Joel Coehoorn · Accepted Answer · 2022-03-22 15:49:40Z

1

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

edited Mar 22, 2022 at 15:49

Joel Coehoorn

418k114 gold badges581 silver badges819 bronze badges

answered Mar 22, 2022 at 15:30

Marcel Marino

1,0023 gold badges19 silver badges35 bronze badges

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

Comments

Aaron Bertrand · Accepted Answer · 2022-03-22 15:40:53Z

0

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).

answered Mar 22, 2022 at 15:40

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Comments

RF1991 · Accepted Answer · 2022-03-22 15:43:49Z

0

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;

answered Mar 22, 2022 at 15:43

RF1991

2,2634 gold badges12 silver badges20 bronze badges

Comments

Joel Coehoorn · Accepted Answer · 2022-03-22 15:45:37Z

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:

Run a query to find information about the desired columns.
Build a new SQL statement on the fly using the results from step 1.
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.

Collectives™ on Stack Overflow

How to combine multiple rows to multiple col name with variable string in mssql?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related