June 18, 2012 at 12:36 pm
I am looking for result in pipe delimited format
IF OBJECT_ID('tempdb..#temp1')> 0
DROP TABLE #temp1
CREATE TABLE #temp1
(
id INT
, NAME VARCHAR(50)
)
INSERT INTO #temp1
SELECT 1 ,'abc'
UNION
SELECT 1,'def'
UNION
SELECT 1 , 'ghi'
UNION
SELECT 2 , 'jkl'
UNION
SELECT 2 , 'tfg'
UNION
SELECT 2 , 'uhi'
UNION
SELECT 2 , 'wer'
SELECT * FROM #temp1 t
I am looking FOR result LIKE
1 abc|def|ghi
2jkl|tfg|uhi|wer
June 18, 2012 at 1:13 pm
Hello krishusavalia,
use this.
SELECT distinct ID,SUBSTRING(NAME1, 1, DATALENGTH(NAME1)/2 - 1) AS [temp]
FROM
#temp1 c CROSS APPLY
(SELECT CONVERT(NVARCHAR(10), NAME) + '|' AS [text()]
FROM #temp1 o
WHERE o.ID = c.ID
ORDER BY o.NAME
FOR XML PATH('')) AS Dummy(NAME1)
Thanks,
June 18, 2012 at 1:35 pm
Thanks , That's what I was looking for.
June 18, 2012 at 5:44 pm
krishusavalia (6/18/2012)
Thanks , That's what I was looking for.
Great. The next questions would be 1) Do you actually understand how it works so you can support it and 2) Do you know what can go wrong with it so you can support it?
--Jeff Moden
June 18, 2012 at 9:51 pm
Jeff Moden (6/18/2012)
krishusavalia (6/18/2012)
Thanks , That's what I was looking for.Great. The next questions would be 1) Do you actually understand how it works so you can support it and 2) Do you know what can go wrong with it so you can support it?
Bwaahahaha! What could go wrong?
Oh wait, perhaps the last record I added to id=2 might break the prior solution?
CREATE TABLE #temp1 (id INT, NAME VARCHAR(50))
INSERT INTO #temp1
SELECT 1 ,'abc'
UNION ALL SELECT 1,'def'
UNION ALL SELECT 1 , 'ghi'
UNION ALL SELECT 2 , 'jkl'
UNION ALL SELECT 2 , 'tfg'
UNION ALL SELECT 2 , 'uhi'
UNION ALL SELECT 2 , 'wer'
UNION ALL SELECT 2 , '&i&'
SELECT id, STUFF(
(SELECT '|' + NAME
FROM #temp1 t2
WHERE t1.id = t2.id
FOR XML PATH(''), root('MyString'), type).value('/MyString[1]','varchar(max)' )
, 1, 1, '') As DelimitedString
FROM #temp1 t1
GROUP BY id
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 12:27 am
dwain.c (6/18/2012)
Jeff Moden (6/18/2012)
krishusavalia (6/18/2012)
Thanks , That's what I was looking for.Great. The next questions would be 1) Do you actually understand how it works so you can support it and 2) Do you know what can go wrong with it so you can support it?
Bwaahahaha! What could go wrong?
Oh wait, perhaps the last record I added to id=2 might break the prior solution?
CREATE TABLE #temp1 (id INT, NAME VARCHAR(50))
INSERT INTO #temp1
SELECT 1 ,'abc'
UNION ALL SELECT 1,'def'
UNION ALL SELECT 1 , 'ghi'
UNION ALL SELECT 2 , 'jkl'
UNION ALL SELECT 2 , 'tfg'
UNION ALL SELECT 2 , 'uhi'
UNION ALL SELECT 2 , 'wer'
UNION ALL SELECT 2 , '&i&'
SELECT id, STUFF(
(SELECT '|' + NAME
FROM #temp1 t2
WHERE t1.id = t2.id
FOR XML PATH(''), root('MyString'), type).value('/MyString[1]','varchar(max)' )
, 1, 1, '') As DelimitedString
FROM #temp1 t1
GROUP BY id
Hi Dwain,
Can you post a link where there is a good explanation on the STUFF Function, the way it is being used here??
I see it all the time, feel like using it, but don't have enough understanding of it to use it. I tried BOL but they have simpler examples and no examples of different ways of using it or different scenarios where it can be used with XML Path().
Would be great if you could post such a link.
June 19, 2012 at 12:32 am
vinu512 (6/19/2012)
Hi Dwain,
Can you post a link where there is a good explanation on the STUFF Function, the way it is being used here??
I see it all the time, feel like using it, but don't have enough understanding of it to use it. I tried BOL but they have simpler examples and no examples of different ways of using it or different scenarios where it can be used with XML Path().
Would be great if you could post such a link.
I wish I could but actually, like you I saw this once and added it to my snippets. I then saw the correction for embedded ampersands (and other characters that XML is sensitive to) and modified my snippet.
While I've gotten comfortable in implementing it (and knowing when it can or should be used), I'm not sure I could explain it all that well.
I've even tried to come up with an alternative. I wrote a recursive CTE once that would do the same thing but it was dog slow. For what it does, it seems to be the best solution around. So I'm sorta content to leave it be just another tool in the tool chest, while I'm off working on more interesting problems. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 3:08 am
dwain.c (6/19/2012)
vinu512 (6/19/2012)
Hi Dwain,
Can you post a link where there is a good explanation on the STUFF Function, the way it is being used here??
I see it all the time, feel like using it, but don't have enough understanding of it to use it. I tried BOL but they have simpler examples and no examples of different ways of using it or different scenarios where it can be used with XML Path().
Would be great if you could post such a link.
I wish I could but actually, like you I saw this once and added it to my snippets. I then saw the correction for embedded ampersands (and other characters that XML is sensitive to) and modified my snippet.
While I've gotten comfortable in implementing it (and knowing when it can or should be used), I'm not sure I could explain it all that well.
I've even tried to come up with an alternative. I wrote a recursive CTE once that would do the same thing but it was dog slow. For what it does, it seems to be the best solution around. So I'm sorta content to leave it be just another tool in the tool chest, while I'm off working on more interesting problems. 🙂
Hmm, I guess I'm at it alone then.
Too bad I have this block in my mind that I do only those things that I thoroughly understand.
So, I'll be searching high and low for something about STUFF. Will definitely update you if I get something useful.
Thanks for the reply.
June 19, 2012 at 8:14 am
vinu512 (6/19/2012)
Can you post a link where there is a good explanation on the STUFF Function, the way it is being used here??
The BOL entry for STUFF (Transact-SQL) gives a perfectly clear explanation of how STUFF is being used here. There's nothing out of the ordinary about it. You're confusing the complexity of the expression used to define the first parameter for STUFF with complexity of the function itself.
The tricky part here comes with the FOR XML clause and the XQuery. Once you understand what those are doing, the STUFF becomes simple.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 19, 2012 at 10:05 pm
drew.allen (6/19/2012)
vinu512 (6/19/2012)
Can you post a link where there is a good explanation on the STUFF Function, the way it is being used here??The BOL entry for STUFF (Transact-SQL) gives a perfectly clear explanation of how STUFF is being used here. There's nothing out of the ordinary about it. You're confusing the complexity of the expression used to define the first parameter for STUFF with complexity of the function itself.
The tricky part here comes with the FOR XML clause and the XQuery. Once you understand what those are doing, the STUFF becomes simple.
Drew
So, you are saying that if I clear my concept of FOR XML and XQUERY then the STUFF function would be easier to understand??..
Thanks for the response Allen.
June 19, 2012 at 10:39 pm
Vinu,
OK, let's give the whole thing a shot step by step:
1. Here's an example of STUFF - it returns 'DEF' by stuffing an empty strings into positions 1-3.
SELECT STUFF('ABCDEF', 1, 3, '')
2. The correlated subquery below contenates the column (NAME) for each row matching the condition of the WHERE clause with a pipe, so if NAME is 'Vinu' the result is '|Vinu', etc.
(SELECT '|' + NAME
FROM #temp1 t2
WHERE t1.id = t2.id
FOR XML PATH(''), root('MyString'), type).value('/MyString[1]','varchar(max)' )
3. The result from the subquery has one too many pipes, so we get rid of the pipe in the first position using the STUFF (position 1 to 1 - the second and third arguments) to STUFF in an empty string.
Does that help?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 10:53 pm
Let me add a couple of steps, because I realized I left something vital out:
4) So you see, the STUFF is really something like this:
STUFF( (subquery), 1, 1, '')
5) Frequently you'll see the FOR XML part look like this:
FOR XML PATH('')
The trouble with this is when there are ampersands (or some other characters that are special to XML) in the data. The last row that I added to the INSERT would yield this:
jkl|tfg|uhi|wer|&i&
Where the & actually appears as 5 characters: &-a-m-p-; (I've had to put them in separated by hyphen because they keep getting translated to & when I save the post).
If it were not for changing the FOR XML part to this:
FOR XML PATH(''), root('MyString'), type).value('/MyString[1]','varchar(max)'
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 11:43 pm
Thank you very much Dwain. I got to understand almost all of it. But the only thing I don't understand is that I can get the results using the following query:
Select Id, Stuff((Select '|' + NAME From #temp1 As t2 Where t1.Id = t2.Id For XML PATH('')), 1, 1, '') As a
From #temp1 t1
Group By id
Then what is the use of:
root('MyString'), type).value('/MyString[1]','varchar(max)'
used in your query?
June 19, 2012 at 11:56 pm
vinu512 (6/19/2012)
Thank you very much Dwain. I got to understand almost all of it. But the only thing I don't understand is that I can get the results using the following query:
Select Id, Stuff((Select '|' + NAME From #temp1 As t2 Where t1.Id = t2.Id For XML PATH('')), 1, 1, '') As a
From #temp1 t1
Group By id
Then what is the use of:
root('MyString'), type).value('/MyString[1]','varchar(max)'
used in your query?
Refer to my point #5.
Try running your query against the test data I provided and mine against it also. Then look at the last record in each case. How are the ampersands appearing in the results set?
Don't ask me by the way, what that last bit added onto the FOR XML does exactly (or how it does it). I don't really know. All I know is that it works.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 12:12 am
Ohh....Ok...so the last part is for the '&i&'.
Now I understand what the last part does.
But, still don't understand how it does it though.
Anyways, thanks a lot Dwain. It was really helpful.:-)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply