Use TSQL to change SQL table data view orientation

Saxitalis
Saxitalis used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle AbrahamsSenior .Net Developer

Commented:
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.

Author

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

Thanks!
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
There are many many examples of this, but a "dynamic sql" query using pivot looks like this:

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(s.Type)
            FROM table1 s
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query = 'SELECT [date], [batch], ' + @cols + ' FROM
            (
                SELECT
                      [date], [batch], [type],[wt]
                 FROM table1
           ) sourcedata
            pivot
            (
                 max([wt])
                FOR [type] IN (' + @cols + ')
            ) p '

--select @query -- use select to inspect the generated sql
execute(@query) -- once satisfied that sql is OK, use execute

Open in new window

I am assuming that the number of "type" columns can and will vary.

From your sample data this is produced: (see http://sqlfiddle.com/#!18/9994f/2 )

|                 date | batch |      a |      f |      h |     jj |      t |
|----------------------|-------|--------|--------|--------|--------|--------|
| 2018-09-01T00:00:00Z |     1 |     50 |    200 | (null) |     25 | (null) |
| 2018-09-01T00:00:00Z |     2 |     25 |     10 | (null) | (null) |     66 |
| 2018-09-02T00:00:00Z |     3 | (null) |     43 |     23 |     12 |     99 |
| 2018-09-02T00:00:00Z |     4 | (null) | (null) | (null) |     56 | (null) |
| 2018-09-03T00:00:00Z |     5 |    333 | (null) |    156 | (null) |     89 |

Open in new window

The generated sql looks like this:
SELECT [date], [batch], [a],[f],[h],[jj],[t] FROM
(
  SELECT
  [date], [batch], [type],[wt]
  FROM table1
) sourcedata
pivot
(
  max([wt])
  FOR [type] IN ([a],[f],[h],[jj],[t])
) p 

Open in new window

Kyle AbrahamsSenior .Net Developer

Commented:
Why doesn't the self join work?

Author

Commented:
I actually solved this on my own using Pivot but will credit your answer for the solution - Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial