Data Rollup

Question

Post reply

Data Rollup

vinay-474901

SSC Veteran

Points: 222
More actions
November 19, 2011 at 8:12 pm

#250781

Hi,
I have data which looks as below:
Yr Month Member Code Desc
2011 01 A HCC1 ABC
2011 01 A HCC2 DEF
2011 01 A HCC3 GHI
2011 02 A HCC4 XYZ
2011 02 A HCC2 DEF
2011 02 A HCC3 GHI
and I want result as
Yr Month Member Count Desc
2011 01 A 3 ABC,DEF,GHI
2011 02 A 4 ABC,DEF,GHI,XYZ
when calculating the count of the member in month of Feb, I need to consider Jan records and get the distinct count of codes and in case of desc, I need to concat it to single string.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

First, please see the first link in my signature line below before your next post. Thanks.

I'd also recommend that you avoid reserved words for column names.

Here's the code for your problem. As always, details are in the comments.

--=======================================================================================

-- Build the test data. Nothing in this section is a part of the solution to the

-- problem. We're just building test data here.

--=======================================================================================

--===== Conditionally drop the test table to make reruns in SSMS easier.

IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL DROP TABLE #TestTable;

GO

--===== Create and populate the test table on-the-fly with data from the original post.

SELECT Yr, Month, Member, Code, [Desc]

INTO #TestTable

FROM (

SELECT '2011','01','A','HCC1','ABC' UNION ALL

SELECT '2011','01','A','HCC2','DEF' UNION ALL

SELECT '2011','01','A','HCC3','GHI' UNION ALL

SELECT '2011','02','A','HCC4','XYZ' UNION ALL

SELECT '2011','02','A','HCC2','DEF' UNION ALL

SELECT '2011','02','A','HCC3','GHI'

) d (Yr, Month, Member, Code, [Desc])

;

--=======================================================================================

-- Solve the problem using a Triangular Join and XML Concatenation methods.

-- Performance will be absolutely terrible because of the Triangular Join

-- if the number of rows get very large but I can't think of another way

-- to do it without a Triangular Join tonight. ;-)

-- The separation of Yr and Month in the original table also makes it impossible to

-- make a SARGable query.

--=======================================================================================

WITH

cteTriangularJoinCount AS

(

SELECT Yr, Month, Member, Code, [Desc],

[Count] = (SELECT COUNT(DISTINCT [Desc]) FROM #TestTable t2 WHERE t2.Yr+t2.Month <= t1.Yr+t1.Month)

FROM #TestTable t1

)

SELECT Yr, Month, Member,

[Desc] = STUFF((

SELECT DISTINCT ','+tjc2.[Desc]

FROM cteTriangularJoinCount tjc2

WHERE tjc2.Yr <= tjc1.Yr

AND tjc2.Month <= tjc1.Month

AND tjc2.Member = tjc1.Member

ORDER BY ','+tjc2.[Desc]

FOR XML PATH('')

),1,1,''),

[Count]

FROM cteTriangularJoinCount tjc1

GROUP BY Yr, Month, Member, [Count]

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vinay-474901 SSC Veteran Points: 222 More actions · Answer 2

Thanks Jeff for your reply. As you have mentioned, this method hits the performance very badly. Is there any other way of doing it?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Actually, there is. I believe that a recursive CTE, much like one that resolves hierarchical paths, might work here. I'm on my way to work and won't be able to try anything else until tonight. Hopefully, one of the "recursive" Ninja's will see this post before then.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sam Bendayan SSC-Addicted Points: 479 More actions · Answer 4

Try this: (I'm using the test data posted by Jeff Moden earlier...tks Jeff 🙂 )

selectyr,

month,

member,

count(*),

(select CASE

WHEN ROW_NUMBER() OVER (ORDER BY t1.yr, t1.month, t1.member) = 1

then ''

else ', '

end + [desc]

from #testtable t2

where t1.yr = t2.yr

and t1.month = t2.month

and t1.member = t2.member

for xml path('')) AS [Desc]

from #testtable t1

group by yr, month, member

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

You're welcome for the test data. Unfortunately, that wonderful bit of XML prestidigitation doesn't consider the data from the previous month like the OP wanted. There should be 4 items listed for February.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Vinay ,

Would you post the CREATE TABLE statement for the columns you posted for the table your data is in please? I need to know what all the data types are for the table before I can try to resolve this problem. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sam Bendayan SSC-Addicted Points: 479 More actions · Answer 7

Yep, you're right...didn't catch that subtlety in the requirements. This should do the trick:

select yr,

month,

member,

(select count(distinct code)

from #testtable t3

where t1.yr = t3.yr

and t1.month >= t3.month

and t1.member = t3.member),

STUFF((SELECT DISTINCT (',' + [desc])

from #testtable t2

where t1.yr = t2.yr

and t1.month >= t2.month

and t1.member = t2.member

for xml path('')), 1, 1, '') AS [Desc]

from #testtable t1

group by yr, month, member

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Sam Bendayan (11/21/2011)
Yep, you're right...didn't catch that subtlety in the requirements. This should do the trick:
select yr,
month,
member,
(select count(distinct code)
from #testtable t3
where t1.yr = t3.yr
and t1.month >= t3.month
and t1.member = t3.member),
STUFF((SELECT DISTINCT (',' + [desc])
from #testtable t2
where t1.yr = t2.yr
and t1.month >= t2.month
and t1.member = t2.member
for xml path('')), 1, 1, '') AS [Desc]
from #testtable t1
group by yr, month, member

I've not tested it but a visual check reveals a triangular join similar to my rather poor first posting and will likely suffer from the very same performance problem.

I'm still waiting for the OP to tell me what the datatypes are for the columns so we can try something that doesn't require a triangular join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

Is there an upper limit to how many descriptions might have to be concatenated?

If so, how many? 10? 20? 100? 1000?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills