sql create new column from rows data

Question

I have a table

date product count
1-1-2003 1 100
1-1-2003 2 200

and I must have on exit:

date product1 product2
1-1-2003 100 200

what kind of sql query can arrange it?

I have nothing to try

Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Filburt, Mar 23 '23 at 17:17

Tushar · Answer 1 · 2023-03-23T17:51:01.440

1

Here you are trying to pivot product column. This should be possible with simple case expression as :

SELECT
  date,
  SUM(CASE WHEN product = 1 THEN count ELSE 0 END) AS product1,
  SUM(CASE WHEN product = 2 THEN count ELSE 0 END) AS product2
FROM myTable
GROUP BY date;

edited Mar 23 '23 at 17:51

answered Mar 23 '23 at 17:15

Tushar

3,527
9
27
49

score 1 · Answer 2 · answered Mar 23 '23 at 17:17

You can use a SQL query with conditional aggregation. Here's a sample query using the CASE statement and GROUP BY clause to pivot the table:

SELECT
  date,
  SUM(CASE WHEN product = 1 THEN count ELSE 0 END) as product1,
  SUM(CASE WHEN product = 2 THEN count ELSE 0 END) as product2
FROM
  some_table
GROUP BY
  date;

You obviously have to replace some_table with the actual name of your table. This query will group the rows by date and create two new columns, product1 and product2, with the respective counts. The CASE statement is used to conditionally sum the count values based on the product number.

score 0 · Answer 3 · answered Mar 23 '23 at 18:57

0

Without giving you the exact query, a PIVOT in SQL Server would accomplish this.

answered Mar 23 '23 at 18:57

Zach Lau

45
5

sql create new column from rows data

3 Answers3