CTE or Pivot Table ??

Question

CTE or Pivot Table ??

singhswat

SSC Veteran

Points: 208
More actions
July 7, 2010 at 10:52 am

#147920

The problem is the Insurance company is getting repeated, there should be just one row for each insurace type, How can i achive this. Very new to SQL T-Query
WITH Consolidated_MonthWise(Insurer, January, February, March, April, May, June, July, August, September, October, November, December)
as
(
SELECT
distinct(I.InsurerDesc) as Insurer,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'January' THEN SumAssured END) AS January,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'February' THEN SumAssured END) AS February,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'March' THEN SumAssured END) AS March,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'April' THEN SumAssured END) AS April,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'May' THEN SumAssured END) AS May,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'June' THEN SumAssured END) AS June,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'July' THEN SumAssured END) AS July,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'August' THEN SumAssured END) AS August,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'September' THEN SumAssured END) AS September,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'October' THEN SumAssured END) AS October,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'November' THEN SumAssured END) AS November,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'December' THEN SumAssured END) AS December
FROM tblPolicyDetails PolD
inner join tblProductDetails PD on PolD.ProductDescID = Pd.ProductDescID
right outer join tblInsurer I on I.InsurerID = PD.InsurerID
--group by I.InsurerDesc, Datename(month,PolD.LoginDate)
GROUP BY I.InsurerDesc,DATENAME(MONTH,LoginDate)
)
select * from Consolidated_MonthWise
O/P is
Insurer January February March April May June July August September October November December
-------------------------------------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
APOLLO MUNICH INSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
BAJAJ ALLIANZ GENERAL INSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
BHARTI AXA GENERAL INSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
CHOLA MANDALAM GENERAL INSURANCE CO.LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
FUTER GENERALI INSURANCE CO .LTD 120000.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
HDFC ERGO GENERAL INSURANCE CCCO.LTD 2052525.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
ICICI LOMBARD GENERAL INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
IFFCO TOKIO INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
LIC NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
LIC NULL NULL NULL NULL NULL 2168410.00 NULL NULL NULL NULL NULL NULL
LIC 34827620.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
NATIONAL ASSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
NEW INDIA ASSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
ORIENTAL INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
RELAINCE GENERAL INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
RELAINCE GENERAL INSURANCE CO. LTD 181273.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
ROYAL SUNDRAM GENERAL INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
STAR HEALTH AND ALLIED INSURANCE CO .LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
TATA AIG GENERAL INSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
UNITED INDIA INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11420 More actions · Answer 1

not sure why you're using a CTE. You can just use a simple select and group by. One of the issues you're having is because you are gouping by the datename which will provide 1 row for each month. try this

SELECT

I.InsurerDesc as Insurer,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'January' THEN SumAssured END) AS January,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'February' THEN SumAssured END) AS February,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'March' THEN SumAssured END) AS March,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'April' THEN SumAssured END) AS April,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'May' THEN SumAssured END) AS May,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'June' THEN SumAssured END) AS June,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'July' THEN SumAssured END) AS July,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'August' THEN SumAssured END) AS August,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'September' THEN SumAssured END) AS September,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'October' THEN SumAssured END) AS October,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'November' THEN SumAssured END) AS November,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'December' THEN SumAssured END) AS December

FROM tblPolicyDetails PolD

inner join tblProductDetails PD on PolD.ProductDescID = Pd.ProductDescID

right outer join tblInsurer I on I.InsurerID = PD.InsurerID

GROUP BY I.InsurerDesc

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

singhswat SSC Veteran Points: 208 More actions · Answer 2

Thanks much... that really worked, should nt hvae kept another group by clause

Mike01 SSChampion Points: 11420 More actions · Answer 3

If you did want to use a pivot table, here's the syntax for that, but it's a little more difficult. Both examples had the same explain plan

Select InsurerID, [January], [February], [March], [April], [May], [June],

[July], [August], [September], [October], [November], [December]

FROM

(select InsurerID, DATENAME(MONTH,LoginDate) LoginDate, SumAssured

FROM #tblPolicyDetails) AS SourceTable

PIVOT

(

sum(SumAssured)

for LoginDate in ([January], [February], [March], [April], [May], [June],

[July], [August], [September], [October], [November], [December])

) as PivotTable;

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

singhswat SSC Veteran Points: 208 More actions · Answer 4

If i put a filter " WHERE DATENAME(YEAR,LoginDate) = '2010'

I lose out on all the insurance company, It should lost out everypossible insurance company if no value the it should be null....

I know i am being too greedy and asking to much, But for sure this is last one ;p

Insurer January February March April May June July August September October November December

-------------------------------------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------

NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

BHARTI AXA GENERAL INSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

LIC 310000.00 NULL NULL NULL NULL 2168410.00 NULL NULL NULL NULL NULL NULL

RELAINCE GENERAL INSURANCE CO. LTD 23.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

STAR HEALTH AND ALLIED INSURANCE CO .LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

LutzM SSC Guru Points: 107049 More actions · Answer 5

The main reason your first approach failed (at least as far I can see):

You have DATENAME(MONTH,LoginDate) in your GROUP BY clause.

This made the SUM() aggregation obsolete leading to the results you ended up with.

Without the DATENAME() function the query returns the aggregated data like Mike demonstrated.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mike01 SSChampion Points: 11420 More actions · Answer 6

I'm not sure what you did or what you are looking for. Can you post what you did? If you are looking for all records for the Year 2010, then you need to put that in the the criteria in the correct place (not sure where you put it). If you want to know all date possibilities, but only populate the year 2010 records and leave all others null, then that's a different story. Let me know what you tried.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

singhswat SSC Veteran Points: 208 More actions · Answer 7

This is what i tried, O/P is all the insurance companies that did some business in 2010, but i want all of them, If they have not done any business be it null

SELECT

I.InsurerDesc as Insurer,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'January' THEN SumAssured END) AS January,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'February' THEN SumAssured END) AS February,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'March' THEN SumAssured END) AS March,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'April' THEN SumAssured END) AS April,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'May' THEN SumAssured END) AS May,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'June' THEN SumAssured END) AS June,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'July' THEN SumAssured END) AS July,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'August' THEN SumAssured END) AS August,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'September' THEN SumAssured END) AS September,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'October' THEN SumAssured END) AS October,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'November' THEN SumAssured END) AS November,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'December' THEN SumAssured END) AS December

FROM tblPolicyDetails PolD

inner join tblProductDetails PD on PolD.ProductDescID = Pd.ProductDescID

RIGHT OUTER JOIN tblInsurer I on I.InsurerID = PD.InsurerID

WHERE DATENAME(YEAR,LoginDate) = '2010'

GROUP BY I.InsurerDesc

Mike01 SSChampion Points: 11420 More actions · Answer 8

That won't work since it will only return 2010 data. You need to add the filter to the sum statement. Keep in mind that since there is no where clause, this will do table scans on all of the tables involved. If the tables are large, this could take a considerable amount of time

SELECT

I.InsurerDesc as Insurer,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'January' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS January,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'February' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS February,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'March' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS March,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'April' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS April,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'May' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS May,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'June' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS June,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'July' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS July,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'August' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS August,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'September' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS September,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'October' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS October,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'November' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS November,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'December' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS December

FROM tblPolicyDetails PolD

inner join tblProductDetails PD on PolD.ProductDescID = Pd.ProductDescID

RIGHT OUTER JOIN tblInsurer I on I.InsurerID = PD.InsurerID

GROUP BY I.InsurerDesc

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

singhswat SSC Veteran Points: 208 More actions · Answer 9

O/P for your query and my is same, Just that the filter is applied at different place.

What i intended to get it " All the insurance companies that are listed with me, The sum of revenues that i could generate out of them.

In that context If there is any insurance company that has done generated 0 revenue it will not be listed in our query output. And this is required. Although xyz company has not given me any business but in the report i want to see null or 0 $.

Thanks for your help till here... I just wanted to explain you the whole scenario 🙂

Mike01 SSChampion Points: 11420 More actions · Answer 10

here is the setup that I am using. The final query returns all insurers regardless of activity. Then if the activity is in the year 2010 it is added otherwise it is not. Can you validate that this is what you are looking for? Notice Insurer 6 doesn't have any activity, but still shows in the total. Also, Insurer 7 has activity, but in year 2011, so it doesn't show either.

create table #tblProductDetails

(ProductID int,

InsurerID int not null,

ProductDesc varchar(30))

go

insert into #tblProductDetails

select 1, 1, 'Product 1' union all

select 2, 1, 'Product 2' union all

select 3, 1, 'Product 3' union all

select 4, 1, 'Product 4' union all

select 5, 2, 'Product 5' union all

select 6, 3, 'Product 6' union all

select 7, 3, 'Product 7' union all

select 8, 5, 'Product 8' union all

select 9, 7, 'Product 9'

create table #tblPolicyDetails

(ProductID int not null,

LoginDate smalldatetime not null,

SumAssured int not null)

insert into #tblPolicyDetails

select 1, '1/1/2010', 100 union all

select 1, '2/1/2010', 200 union all

select 1, '3/1/2010', 300 union all

select 1, '4/1/2010', 400 union all

select 1, '5/1/2010', 500 union all

select 1, '6/1/2010', 600 union all

select 1, '7/1/2010', 700 union all

select 1, '8/1/2010', 800 union all

select 1, '9/1/2010', 900 union all

select 1, '10/1/2010', 1000 union all

select 1, '11/1/2010', 1100 union all

select 1, '12/1/2010', 1200 union all

select 1, '1/1/2010', 100 union all

select 2, '2/1/2010', 200 union all

select 3, '3/1/2010', 300 union all

select 4, '4/1/2010', 400 union all

select 5, '5/1/2010', 500 union all

select 6, '6/1/2010', 60 union all

select 7, '7/1/2010', 70 union all

select 8, '8/1/2010', 80 union all

select 8, '9/1/2010', 90 union all

select 1, '10/1/2010', 100 union all

select 1, '11/1/2010', 110 union all

select 1, '12/1/2010', 120 union all

select 9, '10/1/2011', 100000 union all

select 9, '11/1/2011', 110000 union all

select 9, '12/1/2011', 120000

Create table #Insurer (InsurerID int not null, InsurerDesc varchar(30))

INSERT INTO #Insurer

select 1, 'Insurer 1' union all

select 2, 'Insurer 2' union all

select 3, 'Insurer 3' union all

select 4, 'Insurer 4' union all

select 5, 'Insurer 5' union all

select 6, 'Insurer 6' union all

select 7, 'Insurer 7'

SELECT i.InsurerDesc,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'January' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS January,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'February' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS February,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'March' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS March,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'April' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS April,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'May' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS May,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'June' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS June,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'July' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS July,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'August' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS August,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'September' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS September,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'October' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS October,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'November' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS November,

SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'December' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS December

FROM #tblPolicyDetails PolD

inner join #tblProductDetails PD on PolD.ProductID = Pd.ProductID

RIGHT OUTER JOIN #Insurer I on I.InsurerID = PD.InsurerID

GROUP BY I.InsurerDesc

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

singhswat SSC Veteran Points: 208 More actions · Answer 11

PERFECT 10... waooo!!!

You drilled it so well, I Spent more than 8 hrs and was still struggling to get it rite 🙂

Can i mark this post as answer 🙂

Mike01 SSChampion Points: 11420 More actions · Answer 12

I think you just did!! Glad I could help!

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/