PIVOT QUERY HELP NEEDED

Question

PIVOT QUERY HELP NEEDED

Viewing 9 posts - 31 through 38 (of 38 total)

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

Chris W SSChasing Mays Points: 625 More actions · Answer 1

I was just about to post a very similar question about creating a pivot table with "dynamic" column names when I found this thread. Thanks to all who contributed! In my case, this was exactly what I was looking for before I even asked!!!

Thanks,

Chris.

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 2

Chris...

I don't know if you saw the links... just in case...

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/cross+tab/65048/

I have to appologize for what they did to the code windows. They used to be almost full sized and have recently been "squished". If you copy and past the code windows to Word, first, they'll copy to SSMS or QA much more nicely.

--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)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Chris, congratulations on searching before asking. You'd be surprised how many questions get asked over and over and over, often using the same terminology. 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 4

Hi All,

While working on this PIVOT thing I observed something and I have drawn a conclusion.

My sample query:

declare @test-2 table(myno varchar(25), mydate datetime, mystatus varchar(1), mytype int)

insert into @test-2

select 34001, getdate(),'G',1

union all

select 34001, getdate(),'Y',2

union all

select 34002, getdate(),'G',1

union all

select 34003, getdate(),'G',1

select * from @test-2

select mydate, myno, [1], [2]

from

(select mydate, myno, mytype,mystatus

from @test-2 ) as source

pivot(max(mystatus)

for [mytype] in ([1], [2])

) as test

The result is as expected. But if I change the first insert statement into

select 34001, getdate()+1,'G',1 no grouping happens (seems logical). So my observation was that the PIVOT function applies a grouping on its own for all values that are either not being converted to column or are not part of the aggregate function. Is this correct?

This was not stated explicitly anywhere so I thought better to run through you guys.

martin.edward SSCommitted Points: 1645 More actions · Answer 5

Bob,

You had said a totals row at end is much quicker and easier. How can I add this last row ? Summing up all the columns ?

Tom257 SSChampion Points: 11543 More actions · Answer 6

I missed the last few posts as I was out for a long weekend.

It looks like you got it sorted ok though.

Keep referring to this site and we'll make a Maestro of you sooner than you think 😉

Jeff's articles are always a good place to start.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Yes.

we could at the same time develop another variable with all the same column names with plus signs between them and a constant "AS RowTotal" at the end

What you want is

col1,col2,col3,col1+col2+col3 as RowTotal

So you build two variables at the same time

@str1 = @str1 + (whatever the columnName is) + ','

@str2 = @str2 + (whatever the columnName is) + '+'

And finally

@str1+@str2+' as RowTotal'

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

martin.edward SSCommitted Points: 1645 More actions · Answer 8

Thanks, I have that for the rows. What I wanted to know was how to add up the columns and have the total below them.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

Two ways off the top of my head.

1. GROUP BY with ROLLUP

2. Put your results in a temp table (or make it a cte) and then SELECT * from that and UNION ALL a query which sums the results.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills