use Column name in Query

Question

i have a table like this

Saleperson  Product1     Product2    saleDate
---------------------------------------------
A             500           300     120217
B             600           200     110217

what i want to do is

saleperson    newcolumn   saledate     Sale
--------------------------------------------
A             product1    120217       500
A             product2    120217       300
B             product1    110217       600
B             product2    110217       200

i tried to used pivot table but it seem to be not correct result. i get stuck in using Column name "product1, product2"

CREATE TABLE Sale(
saleperson VARCHAR(02) NOT NULL,
product1 int NOT NULL,
product2 int NOT NULL,
saleDate VARCHAR(10) NOT NULL);

INSERT INTO sale(saleperson,product1,product1,saledate) 
VALUES 
 ("A",500,300,"120217"),
 ("B",600,200,"110217")

It's a great idea to do this and fix the bad table design. The new table is much closer to a good design. You should have a product table, though, and reference the product only by it's ID or product number in the sales table. Same for the sales person. Moreover: Why is your saledate a VARCHAR? And you are even storing a two digit year and use a non-sortable format! Change this to DATE. Then use VARCHAR2, not VARCHAR, in Oracle. — Thorsten Kettner, Nov 07 '17 at 07:01

score 1 · Answer 1 · answered Nov 07 '17 at 06:32

1

You can use unpivot().

SELECT *
FROM sale 
unpivot (Sale FOR product_code IN (product1, product2));

Result:

SALEPERSON  SALEDATE    PRODUCT_CODE    SALE
--------------------------------------------
A           120217      PRODUCT1        500
A           120217      PRODUCT2        300
B           110217      PRODUCT1        600
B           110217      PRODUCT2        200

DEMO

answered Nov 07 '17 at 06:32

zarruq

2,445
2
10
19

thankyou zarruq. i need a dynamic solution because the productname changes monthly. that means this month there can be 2 products : product1,product2. but next month there may be some products more – phalondon Nov 07 '17 at 06:37
@phalondon: This doesn't seem to make sense. Are you saying that next week there can suddenly be new columns in the table? – Thorsten Kettner Nov 07 '17 at 06:57

jophab · Answer 2 · 2017-11-07T06:50:32.447

0

Lets select details of product1 and product2 separately.

Then use UNION ALL to combine the results. SQL Fiddle

select Saleperson, 'product1' as newColumn, saledate,  Product1 as sale
from saleperson 

union all

select Saleperson, 'product2' as newColumn, saledate,  Product2 as sale
from saleperson

edited Nov 07 '17 at 06:50

answered Nov 07 '17 at 06:27

jophab

5,356
14
41
60

That should be `UNION ALL`. There are no duplicates to remove, so don't use `UNION`. The delimiter for string literals is the single quote. Double quotes are for names. – Thorsten Kettner Nov 07 '17 at 06:49

DineshDB · Answer 3 · 2017-11-07T07:34:12.460

0

Try this link, this may helps you. This Suggestion for MS SQL Server:

SQL Server : Columns to Rows

 select SALEPERSON,
  indicatorname PRODUCT_CODE,
  SALEDATE,
  indicatorvalue SALE
from Sale
unpivot
(
  indicatorvalue
  for indicatorname in (product1, product2)
) unpiv;

I just get the result from the above link.

SALEPERSON  PRODUCT_CODE    SALEDATE    SALE
A           product1        120217      500
A           product2        120217      300
B           product1        110217      600
B           product2        110217      200

edited Nov 07 '17 at 07:34

answered Nov 07 '17 at 06:53

DineshDB

5,998
7
33
49

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/17861827) – Saranjith Nov 07 '17 at 07:18
Thank you for the suggestion. Done. – DineshDB Nov 07 '17 at 07:29
1

Oracle is not SQL Server – Nov 07 '17 at 07:33

use Column name in Query

3 Answers3