asked on

Use TSQL to change SQL table data view orientation

Hello Experts,

I would like to use TSQL to perform the following:

1. I have a SQL table (Table1) that looks like this:

Date        Batch Type      Wt
9/1/2018      1      a      50
9/1/2018      1      f      200
9/1/2018      1      jj      25
9/1/2018      2      a      25
9/1/2018      2      f      10
9/1/2018      2      t      66
9/2/2018      3      f      43
9/2/2018      3      h      23
9/2/2018      3      jj      12
9/2/2018      3      t      99
9/2/2018      4      jj      56
9/3/2018      5      a      333
9/3/2018      5      h      156
9/3/2018      5      t      89

2. I would like Table1 above to look like this:

Date      Batch      a      f      h      jj      t
9/1/2018      1      50      200            25
9/1/2018      2      25      10                  66
9/2/2018      3            43      23      12      99
9/2/2018      4                        56
9/3/2018      5      333            156            89

note the Wt column from Table1 is no longer represented in a "Top Down" view in Table2...

Does anyone know how to do this using TSQL?

Thanks!

Kyle Abrahams, PMP

easiest way is with a self join:

select distinct tbl.Date, tbl.BatchId, tblA.Wt as A, tblF.wt as F
 from 
Table1 tbl
left join Table1 tblA on tbl.Date     = tblA.Date and tbl.BatchId = tblA.BatchId and tblA.Type = 'a'
left join Table1 tblF on tbl.Date     = tblF.Date and tbl.BatchId = tblF.BatchId and tblF.Type = 'f'
-- repeat for other tables

Open in new window

though you might be able to do it with a pivot instead.

John Porter

ASKER

The Self Join code does not seem to work for me. What is the Pivot solution?

Thanks!

ASKER CERTIFIED SOLUTION