April 9, 2009 at 2:02 pm
hi
I need a help in writing a query for selecting the output
There are 2 columns
BON TXND
123 41
123 1171
456 41
456 17
789 41
012 1171
345 41
BON and TXND ..2)I want to count the number of combinations of the field TXND for each BON
--
i want the output in this way
OUTPUT
TXNCD TXNCD cnt
41 NULL 2 records
1171 null 1 record
41 1171 1 record
41 17 – 1 record
April 9, 2009 at 2:08 pm
Take a look at Count, and Group By in Books Online. That will do exactly what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 2:12 pm
SELECT BON, TXND, SUM(1) AS CNT
FROM TBL_TEST
GROUP BY BON, TXND
That should do the trick.
Values in the table :
BON TXND
12341
1231171
45641
45617
78941
0121171
34541
12341
3455
0121171
Result :
BON TXND CNT
01211712
12311711
456171
123412
345411
456411
789411
3455 1
April 9, 2009 at 2:20 pm
I want the output in these 3 columns the clolumns for example columns BON as 456 which repeats 2 times so i want the output as
like
TXNCD TXNCD cnt
41 NULL 2 records
1171 null 1 record
41 1171 1 record
41 17 1 record
April 9, 2009 at 2:20 pm
In the code below, the "Combos" cte is used to develop concatenated strings of the TXNCDs for each BON. After that, a simple count of the combinations produces your output. Let me know if you have any questions. 🙂
declare @temp table (BON int, TXND int)
insert into @temp
select 123, 41 union all
select 123, 1171 union all
select 456, 41 union all
select 456, 17 union all
select 789, 41 union all
select 012, 1171 union all
select 345, 41
;with combos as
(select BON,stuff((SELECT ',' + cast(TXND as varchar(10))
FROM @temp d2
WHERE d2.BON = d1.BON -- must match GROUP BY below
ORDER BY TXND
FOR XML PATH('')
),1,1,'') as combination
from @temp d1
GROUP BY BON -- without GROUP BY multiple rows are returned
)
select combination as TXND, count(*) as TXNCDcnt
from combos
group by combination
order by combination
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 9, 2009 at 2:24 pm
Thank you very much....for your help.......you almost solved my problem.......
April 9, 2009 at 2:44 pm
How about this then? I would have had it for you sooner, but your order threw me. Your example shows the combinations in an unusual sequence. While this is possible, I would suggest making all the combinations appear in some order based on the values themselves.
Let me know if your output order in your example is an absolute requirement.
declare @temp table (BON int, TXND int)
insert into @temp
select 123, 41 union all
select 123, 1171 union all
select 456, 41 union all
select 456, 17 union all
select 789, 41 union all
select 012, 1171 union all
select 345, 41
;with tally (N) as
(SELECT TOP 100 ROW_NUMBER() over (order by sc1.id)
FROM Master.dbo.SysColumns sc1)
,combos as
(select BON,stuff((SELECT ',' + cast(TXND as varchar(10))
FROM @temp d2
WHERE d2.BON = d1.BON -- must match GROUP BY below
ORDER BY TXND
FOR XML PATH('')
),1,1,'') as combination
from @temp d1
GROUP BY BON -- without GROUP BY multiple rows are returned
)
,summary as
(select ltrim(rtrim(combination)) as TXND, count(*) as txndCnt
from combos
group by combination
)
select cast(case when TXND like '%,%' then left(TXND,charindex(',',TXND,0)-1) else TXND end as int) as TXND1
,case when TXND like '%,%' then right(TXND,len(TXND)-charindex(',',TXND,0)) else null end as TXND2
,txndCnt
from summary
order by txnd2,txnd1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 9, 2009 at 2:59 pm
I just revised the final select clause in the previous post to match your output example exactly.
It looks like a lot of code, but it runs in a millisecond 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply