May 11, 2012 at 11:36 am
Given a table of zip codes:
ZIP
90000
90010
90020
90025
90030
90035
...
Please advise a CTE query to convert the above into the following string:
['90000','90010','90020','90025','90030','90035'...]
Thanks
May 11, 2012 at 11:51 am
What have you tried so far?
_________________________________
seth delconte
http://sqlkeys.com
May 11, 2012 at 11:58 am
This sounds a lot like homework. I would suggest that a CTE for this is not the best way to go. See this article from Wayne. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2012 at 11:58 am
Sorry I am a newbie in CTE and have seen this:
http://www.sqlservercentral.com/articles/CTE/67974/
A script for doing what I asked would be greatly appreciated.
Thanks.
May 11, 2012 at 12:02 pm
kf2012 (5/11/2012)
Sorry I am a newbie in CTE and have seen this:http://www.sqlservercentral.com/articles/CTE/67974/
A script for doing what I asked would be greatly appreciated.
Thanks.
Why do you have to use a CTE?
_________________________________
seth delconte
http://sqlkeys.com
May 11, 2012 at 12:06 pm
I believe there are advanced CTE pros that could help?
May 11, 2012 at 12:18 pm
You seem to be demanding something for nothing. Don't you think it appropriate that you answer the questions asked? I can give you some code, but I'd like to see the answers too.
By the way, this really isn't that advanced. It also doen't necessarily need to be a CTE.
May 11, 2012 at 12:33 pm
The article you referenced is going the other way. Maybe you can just reverse the logic? :-D:-D:-D
The link I sent you is the easiest way to do this. I can assure you that you already have more than 1 "advanced CTE expert" in this thread that can quite easily solve your problem at least 4-5 different ways.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2012 at 12:35 pm
Thanks a lot, Sean!
May 11, 2012 at 12:37 pm
Just once it would be nice if someone would answer the questions they are asked.
DECLARE @Zip TABLE(ZipCode CHAR(5));
INSERT INTO @Zip (ZipCode)
SELECT '90000' UNION ALL
SELECT '90010' UNION ALL
SELECT '90020' UNION ALL
SELECT '90025';
SELECT
CommaList = STUFF((
SELECT ',' + ZipCode FROM @Zip ORDER BY ZipCode FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')
May 11, 2012 at 12:39 pm
kf2012 (5/11/2012)
I believe there are advanced CTE pros that could help?
Why a CTE, specifically? Is that a requirement of your assignment? It's fine if it this is for homework, we will still help, but only after you have shown you have tried and that you want to be helped. Please provide the following:
1. CREATE TABLE statement to create your zip table.
2. Series of INSERT statements to build some test data.
3. Your desired results based on the test data.
4. Any queries you have tried so far.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2012 at 12:40 pm
Lynn if you keep providing answers there is no incentive for the posters to provide the details for you. They will just learn that you will eventually do it for them. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2012 at 12:46 pm
Sean Lange (5/11/2012)
Lynn if you keep providing answers there is no incentive for the posters to provide the details for you. They will just learn that you will eventually do it for them. 😀
Hey, he already thanked you for the link to Wayne's article, I just put the code together. Oh wait, I didn't do it as a CTE as requested, so I guess he will have to figure out that part.
May 11, 2012 at 12:47 pm
Lynn Pettis (5/11/2012)
Sean Lange (5/11/2012)
Lynn if you keep providing answers there is no incentive for the posters to provide the details for you. They will just learn that you will eventually do it for them. 😀Hey, he already thanked you for the link to Wayne's article, I just put the code together. Oh wait, I didn't do it as a CTE as requested, so I guess he will have to figure out that part.
L:-)L!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply