May 10, 2012 at 4:49 am
I have a table like this,
IDFileCountFileSizeCountry
12150X
29500A
22100B
23200C
2150D
310800P
35200Q
I want output like this,
IDFileCountFileSizeCountry1Country2Country3
12150XNULLNULl
214800ABC
3151000PQNULL
I need only one enrty of each id. For ID – 1, FieldCount – sum of 9+3+2 = 14 (only for top three values), FileSize – Sum of 500+200+100 = 800 (only for top three values) and top three country name in the same row as mentioned above. For ID -2, Country D is not required in the output table and in the calculation of FileConut and FileSize.
Please help me with the query. I am using QL Server 2008.
May 10, 2012 at 6:50 am
This will do it:
--Creating Table
Create Table Ex
(IDint,
FileCount int,
FileSize int,
Country char(1) )
--Inserting Sample Data
Insert Into Ex
Select 1,2,150,'X'
Union ALL
Select 2,9,500,'A'
Union ALL
Select 2,2,100,'B'
Union ALL
Select 2,3,200,'C'
Union ALL
Select 2,1,50,'D'
Union ALL
Select 3,10,800,'P'
Union ALL
Select 3,5,200,'Q'
--Query For your Requirement
;With CTE
As
(Select ID, SUM(FileCount) as FileCount, SUM(FileSize) As FileSize,
Max(Case When rownum = 1 Then Country Else NULL End) As Country1,
Max(Case When rownum = 2 Then Country Else NULL End) As Country2,
Max(Case When rownum = 3 Then Country Else NULL End) As Country3,
MAX(rownum) as rownum
From (Select *, ROW_NUMBER() Over (Partition By Id Order By Id) as rownum From Ex Where Country <> 'D')A
Group By ID)
Select ID, Sum(FileCount) As FileCount, Sum(FileSize) As FileSize, Max(Country1) As Country1,
Max(Country2) As Country2, Max(Country3) As Country3 From CTE
Group By ID
May 15, 2012 at 3:21 am
Thanks vinu512
It works 🙂
May 15, 2012 at 3:37 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply