Problem with crosstab query - 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 2005 Forums

Transact-SQL (2005)

Problem with crosstab query

Author

Topic

mandelbort
Starting Member

8 Posts

Posted - 2008-01-10 : 10:49:44

i have these 3 tables (simplyfied)

Product(ID, Name)
Store(ID, Name)
Availability(ProductID,StoreID, Pieces)

how to write the crosstab procedure statement
to get something as

ProdName Store1 Store2 Store3 Store4
------------------------------------------------
Art1 0 0 0 0
Art2 0 500 600 0
Art3 0 0 0 100

i've tried with:

execute crosstab 'select Name from Product p left outer join Availability a on (p.ID = a.ProductID)
group by p.Name',
'sum(Pieces)',
'ID',
'Store'

but i get this error:

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table '##pivots' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Msg 536, Level 16, State 5, Procedure crosstab, Line 30
Invalid length parameter passed to the SUBSTRING function.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.

if i substitute the 3rd parameter with 'Name' i get a resultset
with all NULL values:

ProdName Store1 Store2 Store3 Store4
------------------------------------------------
Art1 NULL NULL NULL NULL
Art2 NULL NULL NULL NULL
Art3 NULL NULL NULL NULL

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-10 : 11:17:10

This will give you your result set:

declare @Product table (ID int, Name char(10))
declare @Store table (ID int, Name char(10))
declare @Availability table (ProductID int, StoreID int, Pieces int)

insert @Product ( ID, Name )
select 0, 'Art1' union all
select 1, 'Art2' union all
select 2, 'Art3'

insert @Store ( ID, Name )
select 0, 'Store1' union all
select 1, 'Store2' union all
select 2, 'Store3' union all
select 3, 'Store4'

insert @Availability ( ProductID, StoreID, Pieces )
select 0, 0, 10 union all
select 0, 1, 4 union all
select 0, 2, 12 union all
select 0, 3, 9 union all
select 1, 0, 14 union all
select 1, 1, 13 union all
select 1, 2, 7 union all
select 1, 3, 19 union all
select 2, 0, 5 union all
select 2, 1, 1 union all
select 2, 2, 18 union all
select 2, 3, 10 union all
select 3, 0, 2 union all
select 3, 1, 24 union all
select 3, 2, 8 union all
select 3, 3, 16

select * from (
select p.Name as ProdName, s.Name as StoreName, a.Pieces from @Availability a
join @Product p on a.ProductID = p.ID
join @Store s on a.StoreID = s.ID ) a
pivot (sum(Pieces) for StoreName in ( [Store1], [Store2], [Store3], [Store4] ) ) p

http://www.databasejournal.com/features/mssql/article.php/3521101

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-10 : 11:25:32

As far as figuring out why your sproc is not working I would need to see the code for the sproc.

Although I may have found it: http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

Which in that case I would think the best person to troubleshoot the sproc would be RV.

Edit: Although now that Sql Server 2005 has this functionality built in through the PIVOT and UNPIVOT operators I would suggest using them going forward.

mandelbort
Starting Member

8 Posts

Posted - 2008-01-10 : 12:09:25

Oh thank you very much for your reply, i'm almost done with the query,
but the stores number is variable so i've tried something like this:

select * from (
select p.Name as ProdName, s.Name as StoreName, a.Pieces from @Availability a
join @Product p on a.ProductID = p.ID
join @Store s on a.StoreID = s.ID ) a
pivot (sum(Pieces) for StoreName in ( select Name from Store ) ) p

this gives me the error:
Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 40
Incorrect syntax near ')'.

how to the deal with a variable numbers of stores?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 12:15:01

You need build a comma seperated string of stores and use dynamic sql.

declare @sql
set @sql='select * from (
select p.Name as ProdName, s.Name as StoreName, a.Pieces from @Availability a
join @Product p on a.ProductID = p.ID
join @Store s on a.StoreID = s.ID ) a
pivot (sum(Pieces) for StoreName in (''' + @Stores +''' ) ) p'

exec (@sql)

where @stores is the comma seperated list of stores
like 'store1','store2',...

mandelbort
Starting Member

8 Posts

Posted - 2008-01-10 : 12:51:08

quote:
Originally posted by visakh16

You need build a comma seperated string of stores and use dynamic sql.

declare @sql
set @sql='select * from (
select p.Name as ProdName, s.Name as StoreName, a.Pieces from @Availability a
join @Product p on a.ProductID = p.ID
join @Store s on a.StoreID = s.ID ) a
pivot (sum(Pieces) for StoreName in (''' + @Stores +''' ) ) p'

exec (@sql)

where @stores is the comma seperated list of stores
like 'store1','store2',...

ehm... excuse me, i'm not skilled in writing dynamic sql statements

how to populate the @Stores variable with a comma separated list of stores?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-10 : 15:36:01

Learn more about Dynamic Sql: http://www.sommarskog.se/dynamic_sql.html

Complete code:

IF OBJECT_ID ('temp..##Product') is not null
drop table ##Product
create table ##Product (ID int, Name char(10))

IF OBJECT_ID ('temp..##Store') is not null
drop table ##Store
create table ##Store (ID int, Name char(10))

IF OBJECT_ID ('temp..##Availability') is not null
drop table ##Availability
create table ##Availability (ProductID int, StoreID int, Pieces int)

insert ##Product ( ID, Name )
select 0, 'Art1' union all
select 1, 'Art2' union all
select 2, 'Art3'

insert ##Store ( ID, Name )
select 0, 'Store1' union all
select 1, 'Store2' union all
select 2, 'Store3' union all
select 3, 'Store4'

insert ##Availability ( ProductID, StoreID, Pieces )
select 0, 0, 10 union all
select 0, 1, 4 union all
select 0, 2, 12 union all
select 0, 3, 9 union all
select 1, 0, 14 union all
select 1, 1, 13 union all
select 1, 2, 7 union all
select 1, 3, 19 union all
select 2, 0, 5 union all
select 2, 1, 1 union all
select 2, 2, 18 union all
select 2, 3, 10 union all
select 3, 0, 2 union all
select 3, 1, 24 union all
select 3, 2, 8 union all
select 3, 3, 16

declare @sql varchar(max), @columnlist varchar(1000)

select @columnlist = coalesce(@columnlist+', ['+Name+']', '['+Name+']', @columnlist) from ##Store

set @sql = '
select * from (
select p.Name as ProdName, s.Name as StoreName, a.Pieces from ##Availability a
join ##Product p on a.ProductID = p.ID
join ##Store s on a.StoreID = s.ID ) a
pivot (sum(Pieces) for StoreName in ( '+@columnlist+' ) ) p'

exec (@sql)

mandelbort
Starting Member

8 Posts

Posted - 2008-01-11 : 10:28:15

Wonderful, thanks a lot. It works! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources