PIVOT vertical table without Aggregating

Question

PIVOT vertical table without Aggregating

david.c.holley

SSCrazy

Points: 2423
More actions
December 13, 2008 at 4:25 pm

#383568

I have the following vertical table that I need to be able to PIVOT for easier reading
PROJECT PROJECT NAME DATE Category Milestone Date
100001 3PO Feasibility Due 9/10/2007
100001 3PO Executive Presentation 9/30/2007
100001 3PO Requirements Due 1/10/2008
100001 3PO Staff Confirmed 1/15/2008 100001 HCE Feasibility Due 9/10/2007
100001 HCE Executive Presentation 9/30/2007
100001 HCE Requirements Due 1/10/2008
100001 HCE Staff Confirmed 1/15/2008
The table is actually a view that pulls data from the PROJECT and PROJECT DATES tables. Its a one-to-many relationship. Given that the Milestone Date needs to be presented as is - I need to know how to create the pivot without using an aggregate.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

Please post what you'd like the output to look like and, if you provide the data in a readily consumable format like that outline in the link in my signature below, I betting someone which actually try to help you. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david.c.holley SSCrazy Points: 2423 More actions · Answer 2

My bad. Let's just pretend that that post was never posted as I now realize that it was way screwed up.

The Vertical table is

100001 3PO Feasibility Due 9/10/2007

100001 3PO Executive Presentation 9/30/2007

100001 3PO Requirements Due 1/10/2008

100001 3PO Staff Confirmed 1/15/2008

100002 HCE Feasibility Due 9/10/2007

100002 HCE Executive Presentation 9/30/2007

100002 HCE Requirements Due 1/10/2008

100002 HCE Staff Confirmed 1/15/2008

The output should be

Project Name Feasibility Exe Presentation Requriements Staff

100001 3PO 9/10 9/30 1/10 1/15

100002 HCE 9/10 9/30 1/10 1/15

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

It's pretty obvious that you didn't read the article I directed you to. Your problem is simple and so are the methods I directed you to. Post your data in the format I asked for in the article to make my life simple and I'll return the favor in spades.;)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david.c.holley SSCrazy Points: 2423 More actions · Answer 4

If I could write the T-SQL then I'd post it. I am presented the data as it exists in the tables and as I need it displayed. Either help or don't bother.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Heh... you can't take the time to make a dozen insert statements and a create table statement to help me help you... good luck.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 6

david.c.holley (12/14/2008)
If I could write the T-SQL then I'd post it. I am presented the data as it exists in the tables and as I need it displayed. Either help or don't bother.

If you don't want to put time into making it easier for us to help you, how could you ever expect from us that we will put time into helping you? This won't make you very popular you know.

Look for Jeff Moden's articles about Pivot data. Case closed, help yourself.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

david.c.holley (12/14/2008)
If I could write the T-SQL then I'd post it. I am presented the data as it exists in the tables and as I need it displayed. Either help or don't bother.

Not bothering

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

david.c.holley (12/14/2008)
If I could write the T-SQL then I'd post it. I am presented the data as it exists in the tables and as I need it displayed. Either help or don't bother.

You do know that those of us who provide help on these forums do so on our own time and for free. If you want our help, you need to help us out by giving us the things we need to help you. You'll find all that in the article that Jeff wrote, and I have referenced below as well in my signature block.

We don't ask for this to be mean, but our time is just as valuable as yours, and anything you can do to make helping you easier is extremely welcome.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Although I thought I'd not bother with this either, I decided to at least provide you with one solution.

I have also taken the time to create the scripts that you were asked to create and provide. Use this as a template for future requests for help. You will find that those you blew of with "either help or don't bother" will be more willing to help.

create table dbo.Project (

ProjectID int,

ProjectName char(3),

ProjectMileStone varchar(50),

MileStoneDueDate datetime

);

insert into dbo.Project(

ProjectID,

ProjectName,

ProjectMileStone,

MileStoneDueDate)

select 10001,'3PO','Feasibility Due','2007-09-10' union all

select 10001,'3PO','Executive Presentation','2007-09-30' union all

select 10001,'3PO','Requirements Due','2008-01-10' union all

select 10001,'3PO','Staff Confirmed','2008-01-15' union all

select 10002,'HCE','Feasibility Due','2007-09-10' union all

select 10002,'HCE','Executive Presentation','2007-09-30' union all

select 10002,'HCE','Requirements Due','2008-01-10' union all

select 10002,'HCE','Staff Confirmed','2008-01-15';

select * from dbo.Project;

with MyPivot (

ProjectID,

ProjectName,

FeasibilityDue,

ExecutivePresentation,

RequirementsDue,

StaffConfirmed

) as (

select

ProjectID,

ProjectName,

max(case when ProjectMileStone = 'Feasibility Due' then MileStoneDueDate else null end),

max(case when ProjectMileStone = 'Executive Presentation' then MileStoneDueDate else null end),

max(case when ProjectMileStone = 'Requirements Due' then MileStoneDueDate else null end),

max(case when ProjectMileStone = 'Staff Confirmed' then MileStoneDueDate else null end)

from

dbo.Project

group by

ProjectID,

ProjectName

)

select

*

from

MyPivot;

drop table dbo.Project;

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

David:

Are you saying that you do not know how to generate the CREATE TABLE script or the data INSERT's as described in the article?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 11

Lynn Pettis (12/15/2008)
Although I thought I'd not bother with this either, I decided to at least provide you with one solution.
I have also taken the time to create the scripts that you were asked to create and provide. Use this as a template for future requests for help. You will find that those you blew of with "either help or don't bother" will be more willing to help.

Lynn, you're too nice!

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 12

r.hensbergen (12/16/2008)
Lynn, you're too nice!

I agree! WAY too nice.

david.c.holley SSCrazy Points: 2423 More actions · Answer 13

Been busy...

For the record, the discussion groups that I have been apart of do not have stringent rules as to what needs to be included and what does not. Within that framework, the information that I provided would have been sufficient.

I'll be looking for a forum a bit more consistent with my prior experience so as not to waste your time. Thank you though for the code that was posted.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

As Kappa Mikey would say: "W'ver."

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]