6 Replies - 368 Views - Last Post: 24 March 2017 - 03:15 AM

#1 tokei  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 60
  • Joined: 15-December 11

How to do pivot ?

Posted 23 March 2017 - 01:21 AM

Hello all,

I would like to pivot , after searching in the Internet still not success.
Is it possible to do pivot like this (click the link below).

Attachment

Thanks

Edit : how to attach the file ?

This post has been edited by tokei: 23 March 2017 - 01:25 AM

Is This A Good Question/Topic? 0
  • +

Replies To: How to do pivot ?

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13558
  • View blog
  • Posts: 54,106
  • Joined: 12-June 08

Re: How to do pivot ?

Posted 23 March 2017 - 09:13 AM

That's not really a pivot table. Typically pivot tables are for summation and aggregation.

http://www.artfulsof...rytip.php?id=78
Was This Post Helpful? 0
  • +
  • -

#3 tokei  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 60
  • Joined: 15-December 11

Re: How to do pivot ?

Posted 23 March 2017 - 10:31 AM

View Postmodi123_1, on 24 March 2017 - 12:13 AM, said:

That's not really a pivot table.


If not pivot table, how to solve that ?

Thank you
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13558
  • View blog
  • Posts: 54,106
  • Joined: 12-June 08

Re: How to do pivot ?

Posted 23 March 2017 - 11:05 AM

Most likely a join back on itself, a wonky condition on the columns, and listing out each column over and over.

Though I couldn't see the use of this information in this format.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is online

  • Dreaming Coder
  • member icon


Reputation: 6996
  • View blog
  • Posts: 14,634
  • Joined: 16-October 07

Re: How to do pivot ?

Posted 23 March 2017 - 11:56 AM

Quick proof of concept.

create table #test (
  Id int, Thing varchar(30), RepYear int, Dogs int, Cats int
)
go

insert into #test values(1, 'Alice', 2015, 3, 10)
insert into #test values(1, 'Alice', 2016, 2, 22)
insert into #test values(2, 'Bob', 2016, 12, 0)
go

select * from #test
go



Results:
  Id          Thing                          RepYear     Dogs        Cats
----------- ------------------------------ ----------- ----------- -----------
1           Alice                          2015        3           10
1           Alice                          2016        2           22
2           Bob                            2016        12          0

(3 row(s) affected)



To make Id and Thing a single line, you group by and manually deal with the rest of the data. Case and min will usually get you there.

e.g.
select Id, Thing,
    2015 as RepYear,
    min(case when RepYear=2015 then Dogs else null end) as Dogs,
    min(case when RepYear=2015 then Cats else null end) as Cats,
    2016 as RepYear,
    min(case when RepYear=2016 then Dogs else null end) as Dogs,
    min(case when RepYear=2016 then Cats else null end) as Cats
  from #test
  group by Id, Thing



Results:
Id          Thing                          RepYear     Dogs        Cats        RepYear     Dogs        Cats
----------- ------------------------------ ----------- ----------- ----------- ----------- ----------- -----------
1           Alice                          2015        3           10          2016        2           22
2           Bob                            2015        NULL        NULL        2016        12          0
Warning: Null value is eliminated by an aggregate or other SET operation.

(2 row(s) affected)



You should be able to extend that as you like.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#6 tokei  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 60
  • Joined: 15-December 11

Re: How to do pivot ?

Posted 23 March 2017 - 12:12 PM

Actually I would like to do following below :
Attachment

So please advise...

Quote

Quick proof of concept.


I will try your solution. Thanks

This post has been edited by tokei: 23 March 2017 - 12:11 PM

Was This Post Helpful? 0
  • +
  • -

#7 tokei  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 60
  • Joined: 15-December 11

Re: How to do pivot ?

Posted 24 March 2017 - 03:15 AM

Quote

Quick proof of concept.


Thanks , the issue was fixed. Thanks for your solution

This post has been edited by tokei: 24 March 2017 - 03:16 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1