How to pivot table from multiple column to multiple rows

Question

        CodeDt         CodeHeader     Item        Qty     Type    Remark     Attachment
     LK4-033502     RK-K-LK4-032438 IA01001023   2.00   TPR002     2           1.jpeg
     LK4-033502RK   RK-K-LK4-032438 IA01001023RK 2.00   NULL    IA01001023     NULL

Above is my data from Sql server table (using 2008 R2). I want to make it one row only. Here is my expected result:

  CodeDt     CodeHeader        Item          NewItem          Qty       
LK4-033502  RK-K-LK4-032438    IA01001023   IA01001023RK      2.00

How can I achieve that? Here is the relation:

    Row 1 Item = Row 2 Remark, 
    Row 1 Code DT = Row 2 CodeDt+'RK'

What pattern does the data follow from where it is now to where you want it? Make sure you include the types of data and where they need to go in your question. — user7937949
– user7937949, Commented Feb 7, 2018 at 3:48

uzi · Accepted Answer · 2018-02-07 04:17:37Z

4

There are several solutions

1) Using JOIN. It assumes that Type field is null for rows with CodeDt+'RK'

select
    a.CodeDt, a.CodeHeader, a.Item, b.Item, a.Qty
from
    myTable a
    join myTable b on a.Item = b.Remark
where
    a.Type is not null

2) Conditional aggregation

select
    max(case when rn = 1 then CodeDt end)
    , CodeHeader
    , max(case when rn = 1 then Item end)
    , max(case when rn = 2 then Item end)
    , max(case when rn = 1 then Qty end)
from (
    select
        *, rn = row_number() over (partition by CodeHeader order by CodeDt)
    from
        myTable
) t
group by CodeHeader

answered Feb 7, 2018 at 4:17

uzi

4,1561 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

YVS1102 Over a year ago

thanks i'm using your first solution, i modified it a bit and i'm using left join because for some reason the second row item is not exist.

Collectives™ on Stack Overflow

How to pivot table from multiple column to multiple rows

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related