SQL Pivot Table Problem - 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

General SQL Server Forums

New to SQL Server Programming

SQL Pivot Table Problem

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-05-14 : 09:46:16

I have this query that I need to make into a pivot table.

The only fields I need from this query is below, but I need to add a couple cloumns that says 1st Quarter 07, 2nd Quarter 07, 3rd Quarter 07 and 4th Quarter 07. How can I make that possible?

Select

Claim Number,
Total Charges,
Adjusted Savings,
Percent of savings,

FROM vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 09:52:59

On what basis you will group these column values onto four quarter columns? do you have a datefield?

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-05-14 : 10:19:08

In the other columns it will be for each quarter it will be the total charges. The date field is CLM_DOUT. Does that help or do I need to give you more information?

quote:
Originally posted by visakh16

On what basis you will group these column values onto four quarter columns? do you have a datefield?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 10:37:36

[code]SELECT *
FROM
(SELECT Claim Number,
Total Charges,
Adjusted Savings,
Percent of savings,
DATEPART(qq,CLM_DOUT) + ' Quarter '+RIGHT(CAST(DATEPART(yy,CLM_DOUT) AS varchar(4)),2) AS Quarter
FROM vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL)m
PIVOT(SUM([Total Charges]) FOR Quarter IN ([1 Quarter 07], [2 Quarter 07], [3 Quarter 07] and [4 Quarter 07]))p[/code]

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-05-14 : 11:19:56

I am getting an error and I don't know why. The error is.... Incorrect syntax near '+'.

Select *
FROM
(Select clm_id1,clm_tchg,CLM_H30)
DATEPART(qq,CLM_DOUT) + ' Quarter '+RIGHT(CAST(DATEPART(yy,CLM_DOUT) AS varchar(4)),2) AS Quarter
FROM vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL)m
PIVOT(SUM(clm_tchg) FOR Quarter IN ([1 Quarter 07], [2 Quarter 07], [3 Quarter 07] and [4 Quarter 07]))p

quote:

Originally posted by visakh16

SELECT *
FROM
(SELECT Claim Number,
Total Charges,
Adjusted Savings,
Percent of savings,
DATEPART(qq,CLM_DOUT) + ' Quarter '+RIGHT(CAST(DATEPART(yy,CLM_DOUT) AS varchar(4)),2) AS Quarter
FROM vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL)m
PIVOT(SUM([Total Charges]) FOR Quarter IN ([1 Quarter 07], [2 Quarter 07], [3 Quarter 07] and [4 Quarter 07]))p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 12:52:06

quote:

Originally posted by werhardt

I am getting an error and I don't know why. The error is.... Incorrect syntax near '+'.


Select *
FROM 
(Select clm_id1,clm_tchg,CLM_H30,
CAST(DATEPART(qq,CLM_DOUT) AS varchar(2))+ ' Quarter '+RIGHT(CAST(DATEPART(yy,CLM_DOUT) AS varchar(4)),2) AS Quarter
FROM vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL)m
PIVOT(SUM(clm_tchg) FOR Quarter IN ([1 Quarter 07], [2 Quarter 07], [3 Quarter 07] and [4 Quarter 07]))p

quote:

Originally posted by visakh16

SELECT *
FROM
(SELECT Claim Number,
Total Charges,
Adjusted Savings,
Percent of savings,
DATEPART(qq,CLM_DOUT) + ' Quarter '+RIGHT(CAST(DATEPART(yy,CLM_DOUT) AS varchar(4)),2) AS Quarter
FROM vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL)m
PIVOT(SUM([Total Charges]) FOR Quarter IN ([1 Quarter 07], [2 Quarter 07], [3 Quarter 07] and [4 Quarter 07]))p

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-05-16 : 11:37:35

Ok,I revamped my query, I thought I fa different way.....

Select
RecordID,
Type, (Traditional, Teclar, AllClaims)
Year,
Month,
TotalCharges,
AdjustedTotalSavings,
AverageClaim,
PercentSavings,
DHSIncome
From sumClaimsSettlement

What I need the end result to look like...

Type Q1 Q2 Q3 Q4

Traditional
Teclar
All Claims

Under the Quarter cloumn will be Total Charges. What is the better way to do this? Should I go with a Pivot table or make it into a flat file?

quote:

Originally posted by visakh16

quote:

Originally posted by werhardt

I am getting an error and I don't know why. The error is.... Incorrect syntax near '+'.


Select *
FROM 
(Select clm_id1,clm_tchg,CLM_H30,
CAST(DATEPART(qq,CLM_DOUT) AS varchar(2))+ ' Quarter '+RIGHT(CAST(DATEPART(yy,CLM_DOUT) AS varchar(4)),2) AS Quarter
FROM vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL)m
PIVOT(SUM(clm_tchg) FOR Quarter IN ([1 Quarter 07], [2 Quarter 07], [3 Quarter 07] and [4 Quarter 07]))p

quote:

Originally posted by visakh16

SELECT *
FROM
(SELECT Claim Number,
Total Charges,
Adjusted Savings,
Percent of savings,
DATEPART(qq,CLM_DOUT) + ' Quarter '+RIGHT(CAST(DATEPART(yy,CLM_DOUT) AS varchar(4)),2) AS Quarter
FROM vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL)m
PIVOT(SUM([Total Charges]) FOR Quarter IN ([1 Quarter 07], [2 Quarter 07], [3 Quarter 07] and [4 Quarter 07]))p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources