CROSSTAB Result Required - 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)

CROSSTAB Result Required

Author

Topic

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-02-19 : 16:14:49

I have the following fields in my Table

Name,Address1,Address2,City,State,Zip,PurchaseType,Filetype
I want the count of duplicate for Name,Address1,Address2,City,State,Zip
in a cross tab format for each purchase and filetype with PurchaseType in Columns and Filetypes in rows.
This is SQL 2005. Can someone provide an example?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 16:17:27

What is the expected output then?

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-02-19 : 17:09:38

The expected output will be something like this:

FileType1 FileType2 Filetype3 Filetype4 Filetype5
PurchaseType1 2 6 12 0 1
PurchaseType2 0 2 14 0 1
PurchaseType3 3 1 2 0 1
PurchaseType4 8 2 0 0 2

where those numbers are the number of duplicate counts

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 17:23:10

And your sample data ?

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-02-19 : 17:43:29

Something like this

Name Address1 Address2 City State Zip PurchaseType FileType
Name1 Address1 Address2 SampleCity SampleState 'CASH' 'SEC'
Name1 Address1 Address2 SampleCity SampleState 'CREDIT' 'SEC'
Name1 Address1 Address2 SampleCity SampleState 'CHECK' 'TER'
Name2 Address2 Address3 SampleCity2 SampleState2 'CASH' 'SEC'
Name2 Address2 Address3 SampleCity2 SampleState2 'CREDIT' 'PRI'
Name2 Address2 Address3 SampleCity2 SampleState2 'CHECK' 'TER'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources