February 3, 2011 at 7:57 am
I have a sales table with 6 records; salesman 1 has sales in 6 different regions (1 region per record). For example:
ID Salesman Region
1 JOHN USA
2 JOHN GB
3 JOHN DE
4 JOHN BE
5 JOHN ES
6 JOHN FR
Now I want this to be presented as one record, like this
ID Salesman Region
1 JOHN USA
GB
DE
BE
ES
FR
I can create an SQL cursor to get the regions to be presented like this:
ID Salesman Region
1 JOHN USA, GB, DE, BE, ES, FR
What I want to know is how I can ceate an <Enter> after each region in my SQL cursor?
Is there anyone who can help?:-D
February 3, 2011 at 2:00 pm
My first recommendation: since this is a form of presenting the data, I'd trongly vote for doing it at the presentation layer.
If that's not an option, you could use T-SQL to get the data in a list form using FOR XML PATH() (instead of a c.u.r.s.o.r. :sick:)
Something like WITH cte AS
(
SELECT 'a' AS col UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'
)
SELECT DISTINCT REPLACE( STUFF((SELECT ',' + Col FROM cte t2 FOR XML PATH('')),1,1,''),',',CHAR(13))
FROM cte
But like I said: use the presentation layer for stuff like that...
February 3, 2011 at 2:07 pm
nbussel...
Curious, the only time I know I've ever wanted my DB to do this work was when I had to feed a file for a mainframe consumption. Is that what you're dealing with here? If so, what's your text file creation method? There might be easier ways.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 3:12 pm
@craig-2 Farrell I need this fixed in SQL because I am using a Microsoft Word Merge field to display the data in a Word file.
This Word Merge fields presents the data I deliver from SQL.
That is why I need this presentation in SQL because I am not able to alter the presentation in the Word Merge (as far as I know that is).
February 3, 2011 at 3:15 pm
nbussel (2/3/2011)
@LutzM Thanks for your replyI have tested your suggestion and this gives me the output:
a b c
However I wanted the output to be:
a
b
c
It's this part: CHAR(13))
You want CHAR(10) + CHAR(13))
Char 13 is the CR, 10 is the LF (If I remember right). If that doesn't work, reverse 'em. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 3:21 pm
@craig-2 Farrell Thanks for your quick response!
I have tried this as a test:
select 'a'+ CHAR(10) + CHAR(13) +'b'+CHAR(10) + CHAR(13)+ ' c'+CHAR(10) + CHAR(13) as test
I also tried to reverse the CHAR(10) and the CHAR(13). Either way the presentation remains like: a b c
Is there anything else I can try?
February 3, 2011 at 3:39 pm
You're displaying the results as text, not grid, right?
Grid turns CHAR(10) and (13) into hidden characters... and a little testing on my part shows Lutz was right in the first place, sorry Lutz.
Otherwise, if you need this displayed this way in grid format, you're looking at having to do some data manipulation and other things. Not a quick task. I'll have to think about the approach to this some, been a long time since I had to fight with it directly in T-SQL.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 4:37 am
I found a workaround.:-)
Now I use a group by statement and define the individual records as separate columns. Now I can use seperate mailmerge fields to get my data (per column).
Unfortunately this is a lot of work and there will be a lot of merge fields.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply