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!
SaxitalisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
SaxitalisAuthor Commented:
The Self Join code does not seem to work for me. What is the Pivot solution?

Thanks!
0
PortletPaulEE Topic AdvisorCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kyle AbrahamsSenior .Net DeveloperCommented:
Why doesn't the self join work?
0
SaxitalisAuthor Commented:
I actually solved this on my own using Pivot but will credit your answer for the solution - Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.