Crosstabs queries

Hello,
I'm a newbie and have just setup my first sql server based database. I
access it was quite easy creating queries and attaching them to reports.
Moving from access to Access data project, I'm finding it difficult to create
crosstab queries.
Can anyone point me in the right direction, bearing in mind that the
database created is in SQL server 2000 with Access 2000 (ADP) Client.
Thanks in advance.
I'm pretty sure there is but try these following groups as they should have the
exact syntax:
news:microsoft.public.sqlserver.server
news:microsoft.public.sqlserver.misc
news:comp.databases.ms-sqlserver
news:bit.databases.mssql-l
"Hans" wrote:

> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||Its not as easy as creating cross tab queries in Access... You may want to
use sub queries and use those results from the sub queries and turn it into a
case statement to display in the main select quary..! Since you havent give
any idea on what you are trying to cross tab... (columns names, tables,
criteria) its hard to give you a syntex...
"Hans" wrote:

> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||There are several xtab reoprts that needto be created. Example of one that
need to be prepared is.
table : tbl_patientepisodes
fields : EpisodeID - Unique(Index)
DateRefRecved - Date
DateRefAccepted - Date
DateRefNotAccepted - Date
rpt format:-
Title: Referals <from> to <To>
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
No of referals reveived
No Of Referals Accepted
No Not Accepted
This reports will be based on a start and end date specified by the user.
hope this helps.
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||PS sorry forgot to mention a graph need to be prepared with the reports as well
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||"Hans" wrote:

> There are several xtab reoprts that needto be created. Example of one that
> need to be prepared is.
> table : tbl_patientepisodes
> fields : EpisodeID - Unique(Index)
> DateRefRecved - Date
> DateRefAccepted - Date
> DateRefNotAccepted - Date
Perhaps extracting data thusly:
-- Date Referred counts by month
SUM(CASE WHEN MONTH(DateRefRecvd) = 1 THEN 1 else 0 END) as JanRefRcvdCount,
SUM(CASE WHEN MONTH(DateRefRecvd) = 2 THEN 1 else 0 END) as FebRefRcvdCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefRecvd) = 12 THEN 1 else 0 END) as DecRfRcvdCount,
-- Date Acepteded counts by month:
SUM(CASE WHEN MONTH(DateRefAccepted) = 1 THEN 1 else 0 END) as
JanRefAcceptedCount,
SUM(CASE WHEN MONTH(DateRefAccepted) = 2 THEN 1 else 0 END) as
FebRefAcceptedCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefAccepted) = 12 THEN 1 else 0 END) as
DecRfAcceptedCount,

Crosstab question for a "properties" table

Most of the examples for crosstabs use pivot. It looks like aggregations are what they are used for. What about if I have a dynamic properties table. So there is a table Person and a table Property and a table PropertyPersonValue. Is there any way to create a crosstab table of Person with Properties listed in the column heading and the Values in the cells?

Sure, can you post some DDL and sample data?

AMB

|||

CREATE TABLE Person

(

PersonID INT IDENTITY(1,1) NOT NULL,

[Name] VARCHAR(200),

CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED (PersonID)

)

GO

CREATE TABLE PersonProperty

(

PersonPropertyID INT IDENTITY(1,1) NOT NULL,

[Name] VARCHAR(200),

CONSTRAINT [PK_PersonProperty] PRIMARY KEY CLUSTERED (PersonPropertyID)

)

GO

CREATE TABLE PersonPropertyValue

(

PersonID INT NOT NULL,

PersonPropertyID INT NOT NULL,

[Value] VARCHAR(1000),

CONSTRAINT [PK_PersonPropertyValue] PRIMARY KEY CLUSTERED (PersonID,PersonPropertyID),

CONSTRAINT [FK_PersonPropertyValue_PersonID] FOREIGN KEY (PersonID)

REFERENCES [Person],

CONSTRAINT [FK_PersonPropertyValue_PersonPropertyID] FOREIGN KEY (PersonPropertyID)

REFERENCES [PersonProperty],

)

GO

INSERT INTO PersonProperty ([Name]) VALUES ('Eye Color')

INSERT INTO PersonProperty ([Name]) VALUES ('Title')

INSERT INTO Person ([Name]) VALUES ('Joe Smith')

INSERT INTO Person ([Name]) VALUES ('Fred Thompson')

INSERT INTO Person ([Name]) VALUES ('Sally Hamilton')

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Brown'

FROM Person p,PersonProperty pp

WHERE p.Name='Joe Smith'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Blue'

FROM Person p,PersonProperty pp

WHERE p.Name='Fred Thompson'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Green'

FROM Person p,PersonProperty pp

WHERE p.Name='Sally Hamilton'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'CFO'

FROM Person p,PersonProperty pp

WHERE p.Name='Joe Smith'

AND pp.Name='Title'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Vice Presient'

FROM Person p,PersonProperty pp

WHERE p.Name='Fred Thompson'

AND pp.Name='Title'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Sr. Partner'

FROM Person p,PersonProperty pp

WHERE p.Name='Sally Hamilton'

AND pp.Name='Title'

SELECT p.Name,pp.Name AS PropertyName, ppv.Value

FROM Person p

LEFT JOIN PersonPropertyValue ppv

ON p.PersonID=ppv.PersonID

LEFT JOIN PersonProperty pp

ON pp.PersonPropertyID=ppv.PersonPropertyID

|||

Try:

declare @.columns nvarchar(max)

declare @.sql nvarchar(max)

set @.columns = stuff(

(

select ',' + cast(quotename([Name]) as nvarchar(max))

from dbo.PersonProperty

order by [Name]

for xml path('')

), 1, 1, '')

set @.sql = N'

select

*

from

(

SELECT

p.Name,pp.Name AS PropertyName, ppv.Value

FROM

dbo.Person p

LEFT JOIN

dbo.PersonPropertyValue ppv

ON p.PersonID=ppv.PersonID

LEFT JOIN

dbo.PersonProperty pp

ON pp.PersonPropertyID=ppv.PersonPropertyID

) as src

pivot

(

min(Value)

for PropertyName in (' + @.columns + N')

) as pvt

order by

[Name]

'

exec sp_executesql @.sql

go

Result:

Name Eye Color Title Fred Thompson Blue Vice Presient Joe Smith Brown CFO Sally Hamilton Green Sr. Partner

Be careful with sql injection.

PIVOT on Steroids

http://www.sqlmag.com/Articles/ArticleID/94268/pg/3/3.html

AMB

|||Thanks. So I guess pivot is not really used for this sort of thing.

|||

Nope, you are wrong.

He used the PIVOT operator, but he listed or collected all the property values on one variable and given those stored values to your PIVOT operator query...

Print @.SQL

You will get the query which is executed here..