0

I'm sure this is simple for some...but pivot table have always confused me. This should be very simple...and yet it seems to be so hard to find an actual simple explanation on the web LOL.

I have the following temp table with this schema/data. I just need to pivot with no aggregation on the INT rows

CREATE Table #tempTable
    (
        Place VARCHAR(2),
        First1 INT,
        Second1 INT
    )

INSERT INTO #tempTable (Place,First1,Second1)
values ('A1',1,8),('A2',2,5),('B1',4,6),('B2',3,7)

SELECT * from #tempTable

From this:
Place   First1  Second1
A1      1       8
A2      2       5
B1      4       6
B2      3       7

To this:
        A1      A2      B1      B2
First1  1       2       4       3
Second1 8       5       6       7

Thanks so much

Emo
  • 486
  • 4
  • 9
  • 19

1 Answers1

0

This is tricky. One method is to unpivot and then re-pivot or aggregation:

  select v.which,
         max(case when v.place = 'A1' then v.val end) as A1,
         max(case when v.place = 'A2' then v.val end) as A2,
         max(case when v.place = 'B1' then v.val end) as B1,
         max(case when v.place = 'B2' then v.val end) as B2
  from #temptable tt cross apply
       (values ('First1', tt.place, tt.first1), ('Second', tt.place, tt.second1)
       ) v(which, place, val)
  group by v.which;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Oh, someone is going after me, presumably maliciously. – Gordon Linoff Jan 30 '18 at 00:04
  • This get's me exactly what I need. Thanks Gordon! – Emo Jan 30 '18 at 00:14
  • No one is 'going after' you Gordon, but perhaps as an experienced user of the site you should be helping to identify duplicate questions. – Mitch Wheat Jan 30 '18 at 00:46
  • @MitchWheat . . . If someone has a suggestion, they are welcome to include it in a comment. An anonymous downvote does not convey useful information. And, that was not the only downvote that I had at that time. – Gordon Linoff Jan 30 '18 at 02:00
  • The suggestion is " please search for duplicates first rather than answering in an attempt to gain rep" - we've all been there... – Mitch Wheat Jan 30 '18 at 02:37
  • 1
    Hmm...sorry. I did look at a few examples here, but I was still struggling. Maybe I didn't look deep enough. I just know that everything I saw almost had too much data, too many rows...etc in it, and as I was trying to read through a few of them it just got more confusing. My apologies if I missed a post that was just as simple. Wasn't trying to stir things up, and I understand the importance of not having duplicate posts. That being said...this answer was exactly what I was looking for, and was laid out clearly for me, and I really appreciate it! :) – Emo Jan 30 '18 at 03:44
  • Is it possible to do this without the case statement? Akin to excel's paste(pivot) tooling. Sometimes it's much nicer to view the data pivoted... – ColinMac Jan 17 '19 at 19:13