Creating pivot table view

Question

Post reply

Creating pivot table view

Nils ten Hoeve

Newbie

Points: 3
More actions
September 16, 2003 at 7:09 am

#84434

Hi every body, I need some help on a pivot table.
I have the following table CheckResults:
IDChkIDFldID[Value]
111B
212D
313E
421A
522F
641C
742G
843H
I need to make a view that shows a pivot or cross table of CheckResults with the following output:
ChkIdFld1Fld2Fld3
1BDE
2AF<NULL>
4CGH
Thanks for any help you can provide!

Viewing 12 posts - 1 through 11 (of 11 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Try this

select ChkID,

max(case fldid when 1 then value else null end) as Fld1,

max(case fldid when 2 then value else null end) as Fld2,

max(case fldid when 3 then value else null end) as Fld3

from CheckResults

group by

ChkID

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

I am totally fascinated by this solution http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 3

quote:
I am totally fascinated by this solution http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8
Frank
http://www.insidesql.de

So this method has a name. And I just created it for myself one day with no knowledge anyone else had done this. Nice to know.

Edited by - antares686 on 09/16/2003 07:51:06 AM

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 4

It's also in BOL. Search for "cross-tab reports".

Jeremy

PS Antares - you must had told Bill G how to do it.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

quote:
It's also in BOL. Search for "cross-tab reports".
Jeremy
PS Antares - you must had told Bill G how to do it.

Probably stole it from my brain and has a IP clause somewhere in the Windows EULA so he can get away with it.

I didn't even know it was in BOL. I bet thou must folks come to this conclusion thou on their own.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

quote:
quote:
I am totally fascinated by this solution http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8
Frank
http://www.insidesql.de
So this method has a name. And I just created it for myself one day with no knowledge anyone else had done this. Nice to know.
Edited by - antares686 on 09/16/2003 07:51:06 AM

you should check who was first. ...and maybe write a book

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 7

quote:
quote:
quote:
I am totally fascinated by this solution http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8
Frank
http://www.insidesql.de
So this method has a name. And I just created it for myself one day with no knowledge anyone else had done this. Nice to know.
Edited by - antares686 on 09/16/2003 07:51:06 AM
you should check who was first. ...and maybe write a book
Frank
http://www.insidesql.de

I only came up with this a few years ago, if there is a name associated and it is in BOL then I bet they came up with it first. But if I find otherwise I will have to have a sweep of my house done for bugs (MS big Brother OS may be running somewhere around here).

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 8

Frank,

The only problem with the Rozenshtein method and also the sample to be seen in the BOL that those methods work only if you know what will be your columns.

I've seen somewhere a sample which I extended and developed and I've made a general pivot table function.

This example shows a report from a HelpDesk tool where I do not know how many operators I do have and how many days.

Therefore I cannot use the standard case approach described there.

Here is the script. Of cours for some of the column names you have to understand Hungarian but the bust guys are able to...

CREATE proc S_Closed_Tickets_by_date_pivot @DateFrom datetime, @DateTo datetime, @assignment varchar(60)

as

SET ANSI_WARNINGS OFF

set @DateTo = dateadd(dd, 1, @DateTo)

selectclosed_by = cast(full_name as varchar(30)),

close_time = convert(char(10), close_time, 102),

Darab = count(closed_by)

into#FromTable

fromprobsummarym1 P,

operatorm1 O,

groupa1 G

whereclosed_by = O.name

andG.member = O.name

andG.format = 'group.problem'

andG.name = @assignment

andclose_time >= @DateFrom

andclose_time < @DateTo

group

byclosed_by,

full_name,

convert(char(10), close_time, 102)

selectdistinct

pivot = close_time

into#pivot

from#FromTable

declare @sql varchar(8000),

@select varchar(8000)

select @sql = ''

SELECT @sql= @sql + '"' + pivot + '" = ' + 'sum( CASE close_time WHEN ''' + pivot + ''' THEN Darab else 0 END)' + ', ' FROM #pivot

SELECT @sql = left(@sql, len(@sql)-1)

SELECT @select = 'select Név = closed_by, ' + @sql + ' from #FromTable group by closed_by'

EXEC (@select)

drop table #FromTable

drop table #pivot

SET ANSI_WARNINGS ON

GO

Bye

Gabor

Bye
Gabor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

quote:
Frank,
The only problem with the Rozenshtein method and also the sample to be seen in the BOL that those methods work only if you know what will be your columns.
I've seen somewhere a sample which I extended and developed and I've made a general pivot table function.
This example shows a report from a HelpDesk tool where I do not know how many operators I do have and how many days.
Therefore I cannot use the standard case approach described there.
Here is the script. Of cours for some of the column names you have to understand Hungarian but the bust guys are able to...
CREATE proc S_Closed_Tickets_by_date_pivot @DateFrom datetime, @DateTo datetime, @assignment varchar(60)
as
SET ANSI_WARNINGS OFF
set @DateTo = dateadd(dd, 1, @DateTo)
selectclosed_by = cast(full_name as varchar(30)),
close_time = convert(char(10), close_time, 102),
Darab = count(closed_by)
into#FromTable
fromprobsummarym1 P,
operatorm1 O,
groupa1 G
whereclosed_by = O.name
andG.member = O.name
andG.format = 'group.problem'
andG.name = @assignment
andclose_time >= @DateFrom
andclose_time < @DateTo
group
byclosed_by,
full_name,
convert(char(10), close_time, 102)
selectdistinct
pivot = close_time
into#pivot
from#FromTable
declare @sql varchar(8000),
@select varchar(8000)
select @sql = ''
SELECT @sql= @sql + '"' + pivot + '" = ' + 'sum( CASE close_time WHEN ''' + pivot + ''' THEN Darab else 0 END)' + ', ' FROM #pivot
SELECT @sql = left(@sql, len(@sql)-1)
SELECT @select = 'select Név = closed_by, ' + @sql + ' from #FromTable group by closed_by'
EXEC (@select)
drop table #FromTable
drop table #pivot
SET ANSI_WARNINGS ON
GO
Bye
Gabor

one might consider you should declare copyright, write a book and get rich

Nice one!

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 10

For shure. But in the mean time I'm smart and still poor

Bye

Gabor

Bye
Gabor

Wingenious SSCommitted Points: 1648 More actions · Answer 11

There is a generic cross-tab routine, with the ability to specify column headings in a particular order (much like Microsoft Access), included in the FREE package at this URL... http://www.dbaction.com

Creator of SQLFacts, a free suite of tools for SQL Server database professionals.