Query with Product Count - 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

Transact-SQL (2000)

Query with Product Count

Author

Topic

mlmeyer
Starting Member

26 Posts

Posted - 2005-01-13 : 16:25:40

I have a view that lists the CustomerID and Product data as follows:

CustomerID.......Product
1234...............ABC
1234...............DEF
5678...............ABC
9999...............ABC
9999...............DEF
9999...............HIJ

But would like it to appear with the CustomerID and the specific Product names as the headings with the rows being the CustomerID and the count of the products per CustomerID as shown here:

CustomerID.....ABC.....DEF.....HIJ
1234..............1.........1.......0
5678..............1.........0.......0
9999..............1.........1.......1

Any ideas on how to handle this?
Thanks

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-13 : 17:48:59

Sorry I dont remember where I got this post but it works great...
You pass any select statement in, the sum field and the piviot field name and the table name
and it does the cross tab for you

If someone remembers who wrote this please post the post.

CREATE PROCEDURE usp_crosstab
--SAMPLE
--EXECUTE usp_crosstab 'Select Name from AllMyLogins Group by Name', 'Count(Name)', 'Server', 'AllMyLogins'
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
--SET ANSI_WARNINGS OFF
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

GO

mlmeyer
Starting Member

26 Posts

Posted - 2005-01-13 : 18:30:00

When I try this I am getting the error:

Invalid object name 'tempdb.information_schema.columns'

Any ideas on why I am getting that error?

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-01-13 : 18:58:24

quote:
Originally posted by mlmeyer

I have a view that lists the CustomerID and Product data as follows:

CustomerID.......Product
1234...............ABC
1234...............DEF
5678...............ABC
9999...............ABC
9999...............DEF
9999...............HIJ

But would like it to appear with the CustomerID and the specific Product names as the headings with the rows being the CustomerID and the count of the products per CustomerID as shown here:

CustomerID.....ABC.....DEF.....HIJ
1234..............1.........1.......0
5678..............1.........0.......0
9999..............1.........1.......1

Any ideas on how to handle this?
Thanks

Let's check this, mb i'm wrong:

select CustomerID, sum(case when product='ABC' then 1 else 0 ) as ABC,
sum(case when product='DEF' then 1 else 0 ) as DEF,
sum(case when product='HIJ' then 1 else 0 ) as HIJ
from Name
group by CustomerID

mlmeyer
Starting Member

26 Posts

Posted - 2005-01-14 : 11:45:10

I tried that but unfortunetly it did not work.

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-01-14 : 13:44:58

quote:
Originally posted by mlmeyer

I tried that but unfortunetly it did not work.

select CustomerID, sum(case when product='ABC' then 1 else 0 end) as ABC,
sum(case when product='DEF' then 1 else 0 end) as DEF,
sum(case when product='HIJ' then 1 else 0 end) as HIJ
from Name
group by CustomerID

Run this, must work

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-14 : 13:47:31

If your pivot is large or contains an unknown number of pivot columns - you can have a look at Robvolks Dynamic Pivot table article:

http://www.sqlteam.com/item.asp?ItemID=2955

mlmeyer
Starting Member

26 Posts

Posted - 2005-01-14 : 16:14:51

quote:
Originally posted by Hippi

quote:
Originally posted by mlmeyer

I tried that but unfortunetly it did not work.

select CustomerID, sum(case when product='ABC' then 1 else 0 end) as ABC,
sum(case when product='DEF' then 1 else 0 end) as DEF,
sum(case when product='HIJ' then 1 else 0 end) as HIJ
from Name
group by CustomerID

Run this, must work

I tried it this way with the 'end' and it worked great! Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources