May 4, 2016 at 9:51 am
edwardwill (5/4/2016)
I'd no more ask this person to administer my database than I'd ask Donald Trump to run my country.
What about asking Donald Trump to administer your database? https://twitter.com/trumpdba
If you want to complain about the QotD, you should contribute with some yourself. http://www.sqlservercentral.com/Contributions/New/Question
May 4, 2016 at 10:12 am
Lynn Pettis (5/4/2016)
Do this so many times I didn't catch that the STUFF function was missing. Got it right without it there.
Me too.
-- Itzik Ben-Gan 2001
May 4, 2016 at 10:27 am
Alan.B (5/4/2016)
Lynn Pettis (5/4/2016)
Do this so many times I didn't catch that the STUFF function was missing. Got it right without it there.Me too.
Me too. But thanks and kudos to Steve, anyway.
May 4, 2016 at 11:15 am
ThomasRushton (5/4/2016)
What? Nobody's made a crack about Steve stuffing up the QotD? For shame!
ARRGGHH, I deserved that. That's what I get for experimenting and copying code without re-running it.
May 4, 2016 at 11:17 am
Rolling through airports today, but I'll award back points tomorrow.
May 4, 2016 at 11:42 am
Ed Wagner (5/4/2016)
Lynn Pettis (5/4/2016)
Do this so many times I didn't catch that the STUFF function was missing. Got it right without it there.Right there with you. The technique is a good one.
I too looked long and hard to find the missing STUFF command. I just reasoned it out that there is never a case (as far as I know) where you would want a result beginning with comma.
May 4, 2016 at 11:44 am
Lynn Pettis (5/4/2016)
edwardwill (5/4/2016)
I'd no more ask this person to administer my database than I'd ask Donald Trump to run my country.Wow, you have your own country? Must be awesome!
:-D:-D:-D !
May 4, 2016 at 11:51 am
(EP)Z!!!
No need of STUFF. (very old code)
declare @t table (PersonID int, AreaCode varchar(10), Exchange varchar(10), Root
varchar(10))
declare @t1 table (PersonID int, phone varchar(2048))
declare @pn varchar(2048), @Pid int
insert @t
select 10001, '555', '555', '5555'
union all
select 10002, '444', '444', '4444'
union all
select 10001, '555', '555', '5511'
union all
select 10001, '555', '555', '5511'
union all
select 10001, '555', '555', '5522'
union all
select 10001, '555', '555', '5533'
union all
select 10002, '555', '555', '1234'
union all
select 10003, '555', '555', '3333'
declare per cursor for select distinct personid from @t
open per fetch next from per into @pid
while @@FETCH_STATUS =0
begin
select @Pn = IsNull(@pn + ', ','') + (AreaCode+'-'+Exchange+'-'+ root) from @t where
PersonID = @pid
insert @t1 select @pid, @pn
fetch next from per into @pid
set @pn = null
end
close per
deallocate per
select * from @t1
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
May 4, 2016 at 1:01 pm
Raghavendra Mudugal (5/4/2016)
(EP)Z!!!No need of STUFF. (very old code)
No need for cursors, or several statements.
WITH CTE AS(
select distinct personid from @t
)
SELECT PersonID,
phone = STUFF(( SELECT ', ' + AreaCode + '-' + Exchange + '-' + root
FROM @t t
WHERE t.PersonID = CTE.PersonID
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, '')
FROM CTE;
If you use a Surround With snippet, the keystrokes are reduced by half while the performance improves.
Surround With snippet provided at: http://www.sqlservercentral.com/articles/SSMS/138994/
May 4, 2016 at 1:04 pm
Steve Jones - SSC Editor (5/4/2016)
Rolling through airports today, but I'll award back points tomorrow.
Actually, Steve, no one has been asking for their points back. :w00t:
May 4, 2016 at 1:06 pm
Raghavendra Mudugal (5/4/2016)
(EP)Z!!!No need of STUFF. (very old code)
declare @t table (PersonID int, AreaCode varchar(10), Exchange varchar(10), Root
varchar(10))
declare @t1 table (PersonID int, phone varchar(2048))
declare @pn varchar(2048), @Pid int
insert @t
select 10001, '555', '555', '5555'
union all
select 10002, '444', '444', '4444'
union all
select 10001, '555', '555', '5511'
union all
select 10001, '555', '555', '5511'
union all
select 10001, '555', '555', '5522'
union all
select 10001, '555', '555', '5533'
union all
select 10002, '555', '555', '1234'
union all
select 10003, '555', '555', '3333'
declare per cursor for select distinct personid from @t
open per fetch next from per into @pid
while @@FETCH_STATUS =0
begin
select @Pn = IsNull(@pn + ', ','') + (AreaCode+'-'+Exchange+'-'+ root) from @t where
PersonID = @pid
insert @t1 select @pid, @pn
fetch next from per into @pid
set @pn = null
end
close per
deallocate per
select * from @t1
Wow, so much work when it isn't needed.
May 4, 2016 at 1:07 pm
Forget "back points".
Those who got the right answer in spite of the defective code are entitled to BONUS points.
May 4, 2016 at 6:09 pm
I looked at this before morning cofee or breakfast and decided that I couldn't be bothered to guess the answer before the code was fixed - came back at 00:30 (UK time) and there it was, fixed and blatantly obvious what STUFF was doing; and it doesn't seem to be doing anything useful - if the comma isn't wanted, simply not inserting it in the select statement would be a more sensible approach: it seems to me that
WITH CTE AS
(
SELECT DISTINCT
AccountNumber
FROM #TestData
)
SELECT AccountNumber,
CommaList = (
SELECT '' + Value
FROM #TestData
WHERE AccountNumber = CTE.AccountNumber
ORDER BY Value
FOR XML PATH('')
)
FROM CTE
ORDER BY AccountNumber;
would produce identical output and use less compute power. And there may be further computation saving simplifications too.
If the value field started off with comma and the purpose was to remove that comma, the code might sort of make sense, but writing code which inserts a comma just to remove it would surely be crazy?
Tom
May 4, 2016 at 6:26 pm
TomThomson (5/4/2016)
I looked at this before morning cofee or breakfast and decided that I couldn't be bothered to guess the answer before the code was fixed - came back at 00:30 (UK time) and there it was, fixed and blatantly obvious what STUFF was doing; and it doesn't seem to be doing anything useful - if the comma isn't wanted, simply not inserting it in the select statement would be a more sensible approach: it seems to me that
WITH CTE AS(
SELECT DISTINCT
AccountNumber
FROM #TestData
)
SELECT AccountNumber,
CommaList = (
SELECT '' + Value
FROM #TestData
WHERE AccountNumber = CTE.AccountNumber
ORDER BY Value
FOR XML PATH('')
)
FROM CTE
ORDER BY AccountNumber;
would produce identical output and use less compute power. And there may be further computation saving simplifications too.
If the value field started off with comma and the purpose was to remove that comma, the code might sort of make sense, but writing code which inserts a comma just to remove it would surely be crazy?
It removes ONLY the first comma (,) leaving all the other commas in the comma separated list.
May 4, 2016 at 7:08 pm
Lynn Pettis (5/4/2016)
It removes ONLY the first comma (,) leaving all the other commas in the comma separated list.
I must remember to engage brain before commenting - I was thinking of only one Value per AccountNumber, so there was no comma separated list. That "ORDER BY Value" clause makes it pretty obvious that more than one value can be handled for an AccountNumber, so there may be comma separated lists.
Tom
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply