SQL How to transpose table from row to column

Question

I want to write sql query transpose table from row to column as picture below

Thank you very much.

Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — BeaglesEnd, Jun 03 '16 at 08:57

score 2 · Accepted Answer · edited Jun 03 '16 at 08:34

2

I am not using exact names of columns but you can try this,

SELECT GOOD2,
       NAME,
       MAX(CASE WHEN [TYPE] = 'BF' THEN VALUE END) BQTY,
       MAX(CASE WHEN [TYPE] = 'RC' THEN VALUE END) BQTY,
       MAX(CASE WHEN [TYPE] = 'ISU' THEN VALUE END) BQTY,
       MAX(CASE WHEN [TYPE] = 'CF' THEN VALUE END) BQTY
FROM   TABLE1
GROUP BY
       GOOD2,
       NAME

edited Jun 03 '16 at 08:34

Pedram

6,256
10
65
87

answered Jun 03 '16 at 07:31

Chanukya

5,833
1
22
36

1

You're missing a comma after NAME – Rich Benner Jun 03 '16 at 07:38

score 2 · Answer 2 · answered Jun 03 '16 at 07:40

With pivoting:

SELECT  Goods_ID, 
        Name, UM, 
        ISNULL([B/F],0) as [B/F], 
        ISNULL([REC],0) as [REC],
        ISNULL([ISU],0) as [ISU],
        ISNULL([C/F],0) as [C/F]
FROM (
    SELECT Goods_ID, Name, UM, [Type], NULLIF([In-Qty],0) as Qty
    FROM YourTable 
    UNION ALL
    SELECT Goods_ID, Name, UM, [Type],NULLIF([Out-Qty],0)
    FROM YourTable 
    UNION ALL
    SELECT Goods_ID, Name, UM, [Type],NULLIF([B-Qty],0)
    FROM YourTable 
    ) as p
PIVOT (
MIN(Qty) FOR [Type] IN ([B/F],[REC],[ISU],[C/F])
) as pvt

Output:

Goods_ID    Name    UM  B/F     REC     ISU     C/F
CH006       CH006   Kg  11648   11648   0       23296
CH007       CH007   Kg  97584   278400  -315006 60978

score 0 · Answer 3 · answered Jun 03 '16 at 07:22

0

You just want to group by them. Here is a theoretical example:

SELECT * FROM table_name GROUP BY column_name

(you need to include all columns in the table in the order you want)

answered Jun 03 '16 at 07:22

Mark Roll

506
3
6
15

score 0 · Answer 4 · answered Jun 03 '16 at 07:28

0

not tested, but should be something like this...

select goods_id, name, [B/F], REC, ISU, [C/F] from 
    (select goods_id, name, UM, B-Qty from 
    yourtable ) 
    tbl pivot (sum(B-Qty) for UM in ( [B/F], REC, ISU, [C/F] ) 
    as p

answered Jun 03 '16 at 07:28

chungtinhlakho

870
10
21

score 0 · Answer 5 · edited May 23 '17 at 12:07

0

I would say you need to use PIVOT

See the same question with different requirement and almost all ways to achieve your expected output.

edited May 23 '17 at 12:07

Community

1
1

answered Jun 03 '16 at 07:28

Pedram

6,256
10
65
87

SQL How to transpose table from row to column

5 Answers5