SQL: Select, dynamically created values as column

Question

I have to select data from a column then show as these values as another columns. But the struggle is, inside my column always new data will come and new cells will be created.

Product_Table:

ID   NAME
1    apple
2    orange

Selling_Table:

ID   PRODUCT_ID   DATE
1    1            2020-06-12
2    1            2020-05-03
3    2            2020-01-01
4    1            2020-07-23

What I Want

NAME   SELLING_DATE_1   SELLING_DATE_2   SELLING_DATE_3
APPLE  2020-06-12       2020-05-03       2020-07-23
ORANGE 2020-01-01       NULL             NULL

When there is a new date in selling table I want my SQL create another SELLING_DATE dynamically. As you notice when there is no SELLING_DATE data filled with null or we can replace basic text like 'not sold'

Try Googling "dynamic pivot sql server", e.g. https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/ — Richard Hansell, Jun 16 '20 at 13:19
Check it out, it will work.: https://stackoverflow.com/a/52762423/12283105 — Gorkem KARA, Jun 16 '20 at 14:21

score 1 · Accepted Answer · answered Jun 16 '20 at 13:39

You can use window functions and conditional aggregation:

select
    name,
    max(case when rn = 1 then date end) selling_date_1,
    max(case when rn = 2 then date end) selling_date_2,
    max(case when rn = 3 then date end) selling_date_3
from (
    select p.*, s.date, row_number() over(partition by p.id order by s.date) rn
    from product_table p
    inner join selling_table s on s.product_id = p.id
) t
group by id, name

You can expand the query with more columns (that is, more conditional max()s) to handle more dates.

That is great and what i wanted exactly. As you mentioned i will add more conditional 'max()' if i require. Thank you. — Abruzzi, Jun 17 '20 at 10:20

score 0 · Answer 2 · answered Jun 16 '20 at 13:42

I really don't think it's practical to solve your problem that way.

A couple of things you could try instead:

Just select each product once per sale, i.e. join them, which in practice is almost the same as replacing the ID's in your selling_table with the names of the products. That should give you something like:

  PRODUCT    SELLING DATE
  apple      2020-06-12
  apple      2020-05-12
  apple      2020-07-23
  orange     2020-01-01

You can try selecting all dates for each product together and display them as a string (may require some research and work though if you're new to SQL). This answer do a somewhat similar question may perhas help you.
Perhaps you are calling this from some higher level program? If you are using C# for instance, you could probably manipulate whatever result you get pretty easily using e.g. LINQ. That all depends on what the bigger picture looks like though, and how you want to present your final result.

To that end, it would be useful if you could update your question with more info about your overall architecture.

I'm using devexpress xtract report. I have to set my data from SQL with dataset. I need what i tell exactly. Thank you anyway. — Abruzzi, Jun 17 '20 at 10:19
I see. I'm not familiar with Devexpress Extract, but based on a simple google search, it seems there may be options for manipulating data there, just before presenting it. E.g: https://docs.devexpress.com/Dashboard/118802/winforms-designer/create-dashboards-in-the-winforms-designer/providing-data/data-processing-modes . I would try to look into that in any case, and see if I could group the data based on a common field (`product_id` or `product). — Kjartan, Jun 17 '20 at 10:27

SQL: Select, dynamically created values as column

2 Answers2