Insight on creating cross tab query

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

General SQL Server Forums

New to SQL Server Programming

Insight on creating cross tab query

Author

Topic

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-07-07 : 10:46:32

Hi all,

I have a crosstab query in Ms. Access 2007 that I would I like to transition to SQL Server 2008. I not familiar with the cross tab approach in SQL, can you guys please provide some insight

The data is pulled from one table called Contracts

Below is my design view in Ms. Access 2007:
Field: Contract,Contractyear,Contract_ID,Contract year,
Table:
Total: Group, Group, Count, Where
Crosstab:Row, Col, val
Criteria: Contract year >=200601

The result should look like below with a count of the Contract_ID as the value for each month:

Contract 200601 200602 200603 200604.....
94001
95001
96001

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-07 : 12:02:44

something like


SELECT * 
FROM Table
PIVOT (COUNT(Contract_ID) FOR ContractYear IN ([200601],[200602],[200603],..))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-07-07 : 13:14:58

The below works fine but, I don't need to select all the fields. I only need the Contract field, which is comprised by:
Select(Admit.Contract+'-'+Admit.Code)As Contract
When I tried to use this select statment as opposed to select *, I get the "The multi-part identifier could not be bound"

Thanks.

quote:
Originally posted by visakh16

something like

SELECT *
FROM Table
PIVOT (COUNT(Contract_ID) FOR ContractYear IN ([200601],[200602],[200603],..))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-07 : 14:10:21

you should use it in a subquery


SELECT * 
FROM (SELECT Admit.Contract+'-'+Admit.Code As Contract,ConTractYear,other columns... FROM Table)t
PIVOT (COUNT(Contract_ID) FOR ContractYear IN ([200601],[200602],[200603],..))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-07-07 : 19:15:19

Still having trouble with the above. Here is a similar query from Access, how would I transform this with the pivot fucntion?

TRANSFORM IIf(Count([AdmitNum]) Is Null,0,Count([AdmitNum])) AS Expr1
SELECT [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode] AS [Contract Code]
FROM dbo_factAdmissions
WHERE (((dbo_factAdmissions.AdmitCCYYMM)>="200701") AND ((dbo_factAdmissions.AcuteSNFIndAdmit)="a"))
GROUP BY [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode]
ORDER BY [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode]
PIVOT dbo_factAdmissions.AdmitCCYYMM;

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-07-07 : 20:18:19

Can someone please chime in, I really need to figure this out. Thank you.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-08 : 00:23:42

quote:
Originally posted by Briceston

Still having trouble with the above. Here is a similar query from Access, how would I transform this with the pivot fucntion?

TRANSFORM IIf(Count([AdmitNum]) Is Null,0,Count([AdmitNum])) AS Expr1
SELECT [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode] AS [Contract Code]
FROM dbo_factAdmissions
WHERE (((dbo_factAdmissions.AdmitCCYYMM)>="200701") AND ((dbo_factAdmissions.AcuteSNFIndAdmit)="a"))
GROUP BY [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode]
ORDER BY [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode]
PIVOT dbo_factAdmissions.AdmitCCYYMM;

I'm not getting your code... but I can provide you the examples on how to use PIVOT.....
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 01:22:10

quote:
Originally posted by Briceston

Still having trouble with the above. Here is a similar query from Access, how would I transform this with the pivot fucntion?

TRANSFORM IIf(Count([AdmitNum]) Is Null,0,Count([AdmitNum])) AS Expr1
SELECT [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode] AS [Contract Code]
FROM dbo_factAdmissions
WHERE (((dbo_factAdmissions.AdmitCCYYMM)>="200701") AND ((dbo_factAdmissions.AcuteSNFIndAdmit)="a"))
GROUP BY [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode]
ORDER BY [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode]
PIVOT dbo_factAdmissions.AdmitCCYYMM;

I would reckon something like below (I'm not an expert on Access)


SELECT * 
FROM (
SELECT [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode] AS [Contract Code],
[AdmitNum],
dbo_factAdmissions.AdmitCCYYMM
FROM dbo_factAdmissions
WHERE dbo_factAdmissions.AdmitCCYYMM)>='200701'
AND dbo_factAdmissions.AcuteSNFIndAdmit ='a'
)t
PIVOT (Count([AdmitNum]) FOR AdmitCCYYMM IN ([200601],[200602],[200603],..))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-07-08 : 08:59:07

The code you gave me worked with some slight adjustments to the syntax. Thank you for steering me in the right path.
Here is what works with the adjustments:

SELECT *
FROM (
SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code],
factAdmissions.AdmitNum,
factAdmissions.AdmitCCYYMM
FROM factAdmissions
WHERE factAdmissions.AdmitCCYYMM >='200701'
AND factAdmissions.AcuteSNFIndAdmit ='a'
)t
PIVOT (Count([AdmitNum]) FOR AdmitCCYYMM IN ([200601],[200602],[200603]))pvt;

quote:
Originally posted by visakh16

quote:
Originally posted by Briceston

Still having trouble with the above. Here is a similar query from Access, how would I transform this with the pivot fucntion?

TRANSFORM IIf(Count([AdmitNum]) Is Null,0,Count([AdmitNum])) AS Expr1
SELECT [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode] AS [Contract Code]
FROM dbo_factAdmissions
WHERE (((dbo_factAdmissions.AdmitCCYYMM)>="200701") AND ((dbo_factAdmissions.AcuteSNFIndAdmit)="a"))
GROUP BY [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode]
ORDER BY [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode]
PIVOT dbo_factAdmissions.AdmitCCYYMM;

I would reckon something like below (I'm not an expert on Access)

SELECT *
FROM (
SELECT [dbo_factAdmissions].[ContractCode] & '-' & [dbo_factAdmissions].[BenefitPlanCode] AS [Contract Code],
[AdmitNum],
dbo_factAdmissions.AdmitCCYYMM
FROM dbo_factAdmissions
WHERE dbo_factAdmissions.AdmitCCYYMM)>='200701'
AND dbo_factAdmissions.AcuteSNFIndAdmit ='a'
)t
PIVOT (Count([AdmitNum]) FOR AdmitCCYYMM IN ([200601],[200602],[200603],..))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 10:59:35

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources