CODEWITHSUNDEEP
sqlsql-servert-sqlcase-when
user3810679
user3810679

Reputation: 79

how to show row data in column?

I am beginner in T-SQL, I am stuck in a problem in which a row must be produce every day & a table which consists of following columns "DATE","PRICE","CATEGORY 1", "CATEGORY 2". The output must create the column(Combination of "CATEGORY 1", "CATEGORY 2") & show the price. If price is not available than null should be shown. Below is the result which I need to produce 

TABLE
-----------------------------------------------
DATE      | PRICE    | CATEGORY 1 | CATEGORY 2
-----------------------------------------------
20171215    285            Books      Non-Fiction
20171212    390            Gaming     PlayStation 4
20171213    40             Antiques   collectables

OUTPUT

DATE       | Books- Non-Fiction | Gaming - PlayStation 4 | Antiques - collectables  
20171212          NULL                     390                   NULL
20171213          NULL                     NULL                  40
20171215          285                      NULL                  NULL

is it possible to achieve this?

Upvotes: 1

Views: 43

Answers (2)

D-Shih
D-Shih

Reputation: 46229

You can try to use condition aggregate function

CREATE TABLE T(
   DATE INT,
   PRICE INT,
   [CATEGORY 1] VARCHAR(50),
   [CATEGORY 2] VARCHAR(50)

);

INSERT INTO T VALUES (20171215,285,'Books','Non-Fiction');
INSERT INTO T VALUES (20171212,390,'Gaming','PlayStation 4');
INSERT INTO T VALUES (20171213,40 ,'Antiques','collectables');

Query 1:

SELECT DATE,
    SUM(CASE WHEN [CATEGORY 1] = 'Books' and [CATEGORY 2] = 'Non-Fiction'  THEN PRICE end) 'Books- Non-Fiction ',
    SUM(CASE WHEN [CATEGORY 1] = 'Gaming' and [CATEGORY 2] = 'PlayStation 4'  THEN PRICE  end) 'Gaming - PlayStation 4',
    SUM(CASE WHEN [CATEGORY 1] = 'Antiques' and [CATEGORY 2] = 'collectables'  THEN PRICE  end) 'Antiques - collectables'    
FROM T
GROUP BY DATE
ORDER BY DATE

Results:

|     DATE | Books- Non-Fiction  | Gaming - PlayStation 4 | Antiques - collectables |
|----------|---------------------|------------------------|-------------------------|
| 20171212 |              (null) |                    390 |                  (null) |
| 20171213 |              (null) |                 (null) |                      40 |
| 20171215 |                 285 |                 (null) |                  (null) |

Upvotes: 3

Esperento57
Esperento57

Reputation: 17472

try this:

SELECT DATE,
    SUM(CASE WHEN [CATEGORY 1] = 'Books' and [CATEGORY 2] = 'Non-Fiction'  THEN PRICE ELSE null end) 'Books- Non-Fiction',
    SUM(CASE WHEN [CATEGORY 1] = 'Gaming' and [CATEGORY 2] = 'PlayStation 4'  THEN PRICE ELSE null  end) 'Gaming - PlayStation 4',
    SUM(CASE WHEN [CATEGORY 1] = 'Antiques' and [CATEGORY 2] = 'collectables'  THEN PRICE ELSE NULL end) 'Antiques - collectables'     
FROM YOURTABLE
GROUP BY DATE
ORDER BY DATE

Upvotes: 2

Related Questions