Crosstab queries, I am stuck! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Crosstab queries, I am stuck!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-11 : 08:50:37

John Niemiec writes "Hello, I am looked thru the crosstab example on this website for SQL SERVER 7.0 and it did not work(it did not even compile).

How do I crosstab the following data:

product service service_title        charge date

000095  05670   xyz service          100.00 200008
000095  05670   xyz service          150.00 200009
000099  04444   abc service           200.00 200010

to look like:

service_title service   product     200008 200009 200010

xyz service   05670     000095      100.00 150.00   0.00
abc service   04444     000099        0.00   0.00 200.00

Paradox and Access does this very easily, and we do not want to buy or even deal with RAC.

The data fields here (200008, 200009, 200010) can be variable
and can be many years/months in the range (ie. 199901 thru 200201 can be the range etc).

Can anybody help? I hope we have not lost this capability
by going to Sql Server 7.0.

john niemiec"

Nazim
A custom title

1408 Posts

Posted - 2002-04-11 : 09:04:23

i think i could make a living by promoting this article by Rob on Crosstab
http://www.sqlteam.com/item.asp?ItemID=2955

Read it, it should help you a lot

--------------------------------------------------------------

Enquirer
Starting Member

2 Posts

Posted - 2008-07-24 : 07:23:53

Thanks for the direct to this article. But it seems my skills aren't up to the task of interpreting correctly.

I have a table called it# with columns lineno, cusomerno and quantity and want to do the xtab such that I have customerno as row headings (ie one row per customer) and lineno as column headings and sum(quantity) as the values in the table.

Can anyone tell me the syntax I require to use the defined PROCEDURE crosstab.

Many thanks,
Dave

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-24 : 08:19:19

Try...

exec dbo.crosstab 'select product, service, service_title from MyTable
    group by product, service, service_title', 'sum(charge)', 'date', 'MyTable'

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-24 : 08:23:41

Sorry - I was responding to the original request

Try...

exec dbo.crosstab 'select customerno from MyTable
    group by customerno', 'sum(quantity)', '[lineno]', 'MyTable'

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-07-24 : 09:08:11

hi,

try this

Create Table #Temp
(
Product varchar(100),
Services Int,
service_title Varchar(1000),
Charge decimal(18,3),
Date Int
)
Insert into #Temp
Select '000095', 05670, 'xyz service', 100.00, 200008 Union All
Select '000095', 05670, 'xyz service', 150.00, 200009 Union All
Select '000099', 04444, 'abc service' , 1200.00 ,200010 Union All
Select '000099', 05555, 'abc1 service' , 200.00 ,200011

Declare @Sql Varchar(8000), @Str Varchar(8000)
Select @Sql = '', @Str = ''
Select @Sql = @Sql + ',Min(Case when Product = ''' + Product+ ''' and Date = '+ Cast(Date as varchar(1000)) + ' Then charge End ) As "' + Cast(Date as varchar(1000)) + '"'
From (Select Distinct Product, date From #Temp)A
Select @str = @str + 'Select Product, Services, service_title '+@sql+' From #Temp Group By Product, Services, service_title'
print @str
Exec (@str)

Drop Table #Temp

Enquirer
Starting Member

2 Posts

Posted - 2008-07-24 : 10:54:04

Thanks for your speedy replies. The ....

exec dbo.crosstab 'select customerno from MyTable
group by customerno', 'sum(quantity)', '[lineno]', 'MyTable'

..... seems to have done extactly what I was hoping for and saved me much time :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources