April 23, 2012 at 5:35 pm
Hi,
i have a data like below
Personid AccountNo ProductCode Date1
1 111 80 xyz
1 111 80 xyz
1 111 80 xyz
1 222 90 abc
1 222 90 abc
1 222 90 abc
1 222 90 abc
1 223 90 abc
1 223 90 abc
1 223 91 abc
Here i have three combination so i use Group by of Personid, accountno,Productcode,Data1
select PersonId,AccountNumber,ProductCode,Data1,COUNT(*) as countrow
from #temp
group by PersonId,AccountNumber,ProductCode,Data1
i am getting this result set(see below)
Personid AccountNo ProductCode Date1 countrow
1 111 80 xyz 16
1 222 90 abc 24
1 223 90 abc 25
1 223 91 abc 8
Now i need to insert this rows into another table according to Number is Countrows Column
Count row is 16 then i need make group of 8 so i need to insert the row 2 times in temp table.
if count is 24 then we need to insert a rows 3 times in temp table
if count is 25 then we need to insert a rows 4 times in temp table
if count is 8 then we need to insert a rows 1 times in temp table
in another temp table i will get below rows
Personid AccountNo ProductCode Date1
1 111 80 xyz
1 111 80 xyz
1 222 90 abc
1 222 90 abc
1 222 90 abc
1 223 90 abc
1 223 90 abc
1 223 90 abc
1 223 90 abc
1 223 91 abc
Thank you very much in advance for any Help..
i do not want to use cursor in this scenario.
April 23, 2012 at 5:51 pm
Can you post the complete Create table statement and Insert statements for your tables? Please see the link in my signature for directions.
The reason i ask is im wondering how you get to a count of 16 for (1 111 80 xyz) when there are only 10 rows of data in your post.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 23, 2012 at 6:08 pm
Unnati,
I have seen you on another thread and i remember asking you to provide DDL so that people can jump on the thread without wasting yours as well as our time.
That being said, i set up the data fro you this time.
Here is the sample data set
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp ;
IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL
DROP TABLE #Temp2 ;
CREATE TABLE #Temp
(
Personid INT
,AccountNo INT
,ProductCode INT
,Date1 VARCHAR(10)
)
INSERT INTO #Temp(Personid ,AccountNo ,ProductCode, Date1)
SELECT Smple.Personid , Smple.AccountNo , Smple.ProductCode , Smple.Date1
FROM
(
SELECT 1, 111, 80, 'xyz' ,16
UNION ALL SELECT 1, 222, 90, 'abc' , 24
UNION ALL SELECT 1, 223, 90, 'abc' ,25
UNION ALL SELECT 1, 223, 91, 'abc' ,8
) Smple (Personid ,AccountNo ,ProductCode, Date1,ct)
CROSS JOIN (SELECT TOP 25 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master.sys.columns) Nums(N)
WHERE Nums.N <= Smple.ct
And here is the code for your requirement:
IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL
DROP TABLE #Temp2 ;
DECLARE @BaseDivisor SMALLINT = 8;
; WITH GroupedData AS
(
SELECT Smple.Personid , Smple.AccountNo , Smple.ProductCode , Smple.Date1
,CountRow = ((( COUNT(*) - 1 ) / @BaseDivisor ) + 1)
FROM #Temp Smple
GROUP BY Smple.Personid , Smple.AccountNo , Smple.ProductCode , Smple.Date1
)
, Nums(N) AS
(
SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master.sys.columns
)
SELECT G.*
INTO #temp2
FROM GroupedData G
CROSS JOIN Nums
WHERE Nums.N <= G.CountRow
Tell us if that worked. Also please follow the forum etiquettes on how to post data (just look how i set up data). Happy monday/tuesday 🙂
April 23, 2012 at 8:06 pm
Awesome Logic..
Thank you very for your quick reply.
it works good.
i got it.
Thank you very much for your help once again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply