SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Insight on creating cross tab query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Briceston
Yak Posting Veteran

54 Posts

Posted - 07/07/2013 :  10:46:32  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/07/2013 :  12:02:44  Show Profile  Reply with Quote
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
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 07/07/2013 :  13:14:58  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/07/2013 :  14:10:21  Show Profile  Reply with Quote
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
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 07/07/2013 :  19:15:19  Show Profile  Reply with Quote
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;
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 07/07/2013 :  20:18:19  Show Profile  Reply with Quote
Can someone please chime in, I really need to figure this out. Thank you.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 07/08/2013 :  00:23:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/08/2013 :  01:22:10  Show Profile  Reply with Quote
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
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 07/08/2013 :  08:59:07  Show Profile  Reply with Quote
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



Edited by - Briceston on 07/08/2013 09:04:14
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/08/2013 :  10:59:35  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000