August 17, 2011 at 8:40 am
i got it...i have to use the same query in Sql2000 too..:(
we used ROWNUMBER()...
karthik
August 17, 2011 at 8:43 am
is there any way to generate the sequencte number without using ROWNUMBER() or CREATE TABLE WITH IDENTITY on the fly?
I can use something like below
select no from
(select 1 as no
union
select 2
union
select 3
union
select 4
)a
but it won't be useful for my requirement.
karthik
August 17, 2011 at 9:15 am
SQL2000?
Good Luck!
Ok, you will need to use temp table:
SELECT IDENTITY(int,1,1) idn,
0 AS RN,
ID,
val = SUBSTRING(val, t.N, CHARINDEX(',', val+ ',', t.N) - t.N)
INTO #t
FROM W cross join dbo.Tally t
WHERE t.N <= DATALENGTH(val)+1
AND SUBSTRING(',' + val, t.N, 1) = ','
ORDER BY ID, N
-- nice trick will turn all sequential numbers to 1,2,3,4 :-)
UPDATE #t SET RN = case when idn & 3 = 0 then 4 else idn & 3 end
--final qry
select ID
,max(case when rn = 1 then val else null end) as colA
,max(case when rn = 2 then val else null end) as colB
,max(case when rn = 3 then val else null end) as colC
,max(case when rn = 4 then val else null end) as colD
from #t
group by ID
order by ID
August 18, 2011 at 12:18 am
yeah..i have also tried the same...Thanks a lot for all your help.
is there any way to achieve my requirement without using temporary table/IDENTITY/ROW_NUMBER?
karthik
August 18, 2011 at 2:49 am
karthikeyan-444867 (8/18/2011)
yeah..i have also tried the same...Thanks a lot for all your help.is there any way to achieve my requirement without using temporary table/IDENTITY/ROW_NUMBER?
Is any problem with using temporary table/IDENTITY? As you run on SQL2000 you cannot use ROW_NUMBER.
August 18, 2011 at 5:14 am
yes. the requirement got changed little bit yesterday night. the business user wants to add some more column in the report.
yes, say for example we GROUPED or generated numbers based on ID.
Now..we have to include acc_no also in the report.
say for example,
ID acc_no text
100 a1 D,20987987.5%,A account is being linked to B account
100 a1 D,20987987.5%,A account is being linked to B account
100 b1 R,2.5%,A account is being linked to B account
100 b1 R,2.5%,A account is being linked to B account
100 b1 R,25%,A account is being linked to B account
100 c1 X,25%,A account is being linked to B account
100 c1 X,25%,A account is being linked to B account
I have split the columns based on ID as well as acc_no. if we use row_number() function, it is simple for us to generate th sequnece no based on ID,acc_no. But in sql 2000, it is very hard (since sql2000 doesn't support row_number() function).
so i found some work around for this...
i just included the N (the position of the delimter) and wrote a seperate query (used Nth maximum logic) something like below...
select distinct ID,
acc_no,
ColA= (select splitted_val from #split a
where a.ID = z.ID
and a.acc_no = z.acc_no
and number =
(select distinct number from #split a where 1 = (select count(distinct number) from #split b where b.number <= a.number)
)
),
ColB= (select splitted_val from #split a
where a.ID = z.ID
and a.acc_no = z.acc_no
and number =
(select distinct number from #split a where 2 = (select count(distinct number) from #split b where b.number <= a.number)
)
),
ColC= (select splitted_val from #split a
where a.ID = z.ID
and a.acc_no = z.acc_no
and number =
(select distinct number from #split a where 3 = (select count(distinct number) from #split b where b.number <= a.number)
)
),
ColD= (select splitted_val from #split a
where a.ID = z.ID
and a.acc_no = z.acc_no
and number =
(select distinct number from #split a where 4 = (select count(distinct number) from #split b where b.number <= a.number)
)
)
from #split z
karthik
August 19, 2011 at 12:21 am
Heh... it would be nice if you'd let us know first when you need 2000 code in a 2005 forum. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2011 at 12:44 am
hey Jeff...
Actually we are using both the version. i.e. 2005 and 2000. moreover the logic needs to be implemented in both the server. Thats why i asked for 2000 too. Since most of the people visit 2005 & 2008 forums, i just asked here itself for 2000 too..
karthik
August 19, 2011 at 12:50 am
Yep... I understand and appreciate that. You just asked about 2000 awfully late in the game.
Why not just use the tally Table splitter against a Temp Table that has an Identiy column instead of going through the subquery hell you're going through? That would very easily allow you to do a cross-tab to repivot the information into columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2011 at 1:11 am
yup. The below query worked fine.
create table W
(
ID int,
acc_no varchar(5),
val varchar(255)
)
insert into W
select 100,'a1','D,20987987.5%,,A account is being linked to B account'
union all
select 100,'a2','D,20987987.5%,,A account is being linked to B account'
union all
select 100,'b1','R,2.5%,,A account is being linked to B account'
union all
select 100,'b2','R,2.5%,,A account is being linked to B account'
union all
select 100,'b3','R,25%,,A account is being linked to B account'
union all
select 100,'c1','X,25%,,A account is being linked to B account'
union all
select 100,'c2','X,25%,,A account is being linked to B account'
SELECT IDENTITY(int,1,1) idn,
0 AS RN,
ID,
acc_no,
val = SUBSTRING(val, t.seq, CHARINDEX(',', val+ ',', t.seq) - t.seq)
INTO #t
FROM W cross join dbo.Tally t
WHERE t.seq <= DATALENGTH(val)+1
AND SUBSTRING(',' + val, t.seq, 1) = ','
ORDER BY ID,acc_no, seq
-- nice trick will turn all sequential numbers to 1,2,3,4
UPDATE #t SET RN = 0 case when idn & 3 = 0 then 4 else idn & 3 end
--final qry
select ID ,acc_no
,max(case when rn = 1 then val else null end) as colA
,max(case when rn = 2 then val else null end) as colB
,max(case when rn = 3 then val else null end) as colC
,max(case when rn = 4 then val else null end) as colD
from #t
group by ID,acc_no
order by ID,acc_no
But...the only part which i don't understand is..
UPDATE #t SET RN = 0 case when idn & 3 = 0 then 4 else idn & 3 end
what it will do actually?
karthik
August 19, 2011 at 1:18 am
It shows zero only 4,8,12,16 etc., (4 and its multiplication 4*1, 4*2, 4*3 etc.,)
how?
select 5 &3 = 1
when it comes to 5, againt the sequence reset to 1. how?
select 6 & 3 = 2
select 7 & 3 = 3
select 8 & 3 = 0
?
karthik
August 19, 2011 at 1:23 am
It seems like decimal to binary conversion and binary to decimal happening internally..but i am not still that much clear behind this UPDATE statement.
is it something the equivalent binary number of idn column and binary number of 3 (11) added together?
karthik
August 19, 2011 at 1:52 am
That's because someone made it difficult. See if you understand the following modifications...
create table W
(
ID int,
acc_no varchar(5),
val varchar(255)
)
insert into W
select 100,'a1','D,20987987.5%,,A account is being linked to B account'
union all
select 100,'a2','D,20987987.5%,,A account is being linked to B account'
union all
select 100,'b1','R,2.5%,,A account is being linked to B account'
union all
select 100,'b2','R,2.5%,,A account is being linked to B account'
union all
select 100,'b3','R,25%,,A account is being linked to B account'
union all
select 100,'c1','X,25%,,A account is being linked to B account'
union all
select 100,'c2','X,25%,,A account is being linked to B account'
drop table #t
SELECT IDENTITY(int,0,1) idn, --LOOK
0 AS RN,
ID,
acc_no,
val = SUBSTRING(val, t.N, CHARINDEX(',', val+ ',', t.N) - t.N)
INTO #t
FROM W cross join dbo.Tally t
WHERE t.N <= DATALENGTH(val)+1
AND SUBSTRING(',' + val, t.N, 1) = ','
ORDER BY ID,acc_no, N
SELECT * FROM #T
-- Better trick will turn all sequential numbers to 0,1,2,3
UPDATE #t SET RN = idn % 4 --LOOK
--final qry
select ID ,acc_no
,max(case when rn = 0 then val else null end) as colA
,max(case when rn = 1 then val else null end) as colB
,max(case when rn = 2 then val else null end) as colC
,max(case when rn = 3 then val else null end) as colD
from #t
group by ID,acc_no
order by ID,acc_no
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2011 at 3:46 am
Jeff Moden (8/19/2011)
That's because someone made it difficult. See if you understand the following modifications...
Not at all...
It was because someone wanted to turn 1,2,3,4,5,6,7,8,... into 1,2,3,4,1,2,3,4,...
not 0,1,2,3,4,5,6,7,... into 0,1,2,3,4,5,6,7,... 😀
If I'd used "simple" RN % 4, it would risk not to catch the attention of OP. :hehe:
Seriously, I just copied it from query I've written a day ago where records from staging table (populated by existing production process which I couldn't change) was required to be renumbered in exactly this way from 1-based identity. It is just pure coincidence..
Anyway, I guess OP works for very rich and and in the same time adventures organisation, as support for SQL2000 from MS is now cost quite a lot and not guaranteed in many aspects...
August 19, 2011 at 4:18 am
why you used "3" ?
If I use any other number, it is not working as expected. what is the reason behind the usage of 3?
select 1 & 3 = 1 how?
select 2 & 3 = 2 how?
select 3 & 3 = 3 how?
select 4 & 3 = 0 how?
how sql server works internally for the above one?
will it convert something like below for select 1 & 3?
1 -> equivalent binary number -> 1
3 -> equivalent binary number -> 11
1 + 11 = 100
100
= 0 * 2 power 0 = 0
= 0 * 2 power 1 = 0
= 1 * 2 power 2 = 4
so the equivalent decimal number is 4. right? how it shows as 1?
Jeff,
I can easily understand %4. Let me apply this and get back to you.
karthik
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply