Build Concatenated List of Values
-
I have a stock table something like this:
Loc Item Bin
STK1 28 C015
STK1 28 C016
STK1 45 A004
For reporting purposes, I want to transform the data to look like this:
Loc Item Bin
STK1 28 C015,C016
STK1 45 A004
I'm looking for a set-based solution to this if possible. I know it is easy enough to achieve using a cursor, but I'm after another method.
If someone can help me, that would be great.
Many thnaks
Rowan
-
I present the easiest and most profound solution:
http://www.rac4sql.net/onlinehelp.asp?topic=236
enjoy
-
Thanks for your response. I have been around this industry for twenty years but I am still amazed by the engenuity and endeavour that the net has exposed us to.
What an interesting little tool and I could appreciate immediatley how this could resolve the problem. But I am looking for a more generic approach - I work for a software developer and while Rac is not expensive, it is not going to be an option. The head developer is simply going to tell me to get over it and use a cursor.
However for me, that is not going to be acceptable - this is partially an intellectual challenge as well as being a practical problem to resolve. I dislike cursors in general and try to avoid their use wherever possible. Some of my colleagues are less discerning when it comes to creating code - quickest way to get the job done and to hell with the expense.
You have opened up another train of thought however - I had not been looking at this problem from a pivot perspective as I have not used them much. But if anyone else has anything to add, I will only be too happy to receive the advice.
Thanks again for taking the time to respond.
Rowan
-
Hello Rowan,
What a pleasure to meet a kindred spirit! That you have not lost your intellectual curiosity speaks volumns. It is an industry that all to often rewards complacency and freezes curiosity with vendor hype. Contact me anytime (via http://www.rac4sql.net) for a free license. The mind is a terrible thing to waste
Perhaps you'll also find something of interest @:
best,
steve
-
While this requires a UDF, it doesn't require a cursor.
Create the following UDF:
CREATE FUNCTION dbo.fn_GetBinList
(
@Loc varchar(50)
,@Item varchar (50)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @buffer VARCHAR(8000)
SELECT
@buffer = IsNull(@buffer + ',', '') + Bin
FROM
dbo.TableName
WHERE
Loc = @Loc
AND Item = @Item
RETURN @buffer
END
GO
Then use the code:
SELECT DISTINCT
Loc
,Item
,dbo.fn_GetBinList(Loc, Item)
FROM
dbo.TableName
-
Good tool... bad answer ... you still haven't answered the "generic" question. David got it, though...
(and it appears that you are not paying for this form of advertising which might tick off the owners of this site)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I would point out that 'generic' is implied by the concept of a 'utility'.
As for your repeating yourself, perhaps you feel that something like RAC robs you of coding. In that case perhaps you should try 'morse'
Help yourself to a trial version of RAC on me.
best,
-
If you wish to delve into semantics, I suppose you are correct. Still, you didn't meet the spirit of the original posting nor have any of the 23 posts you've submitted so far... you've merely attempted to push your product. Although your promises are encouraging, I’ve seen nothing of performance comparisons, so far. Perhaps if you wrote the "generic" solution that was actually requested and then did the same using your product accompanied by a "time to develop" and performance comparison in a mega-record environment, your attempts at freeload-marketing on a forum would "RAC" up a few more points with your target audience. Being the smart fellow that I think you could be, I’m sure that would be no problem for you, at all.
In the meantime, your observation of a duplicate cut and paste and your generally smart-assed comment about "robs you of coding" is not the way to make friends, at least not with me. Examples of good code, both the "generic" way and using your product, will go a lot further. I've evaluated a lot of software... most of it turns out to be crap one way or another or is nothing more than an expensive replacement for what already exists so I have no real incentive (yet) to test your software... incite me to actually download your trial software and test it by giving demonstrable examples… you haven’t, so far.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Being a sucker for reasonableness let me respond. Rac is an attempt at abstraction. It is an attempt to by pass the drudgery of coding 'complex' problems in the hopes that it is easier to arrive at a solution to a problem unimpeded by the necessity to dwell on the means to attain it. This is the basic idea of abstraction.
In actuality it is overkill for the problem presented by the op. But there are no free lunches. The price to be paid for this kind of abstraction is performance. The expectation' of performance in the industry and in Sql Server is profund. But that expectation is why MS does 'not' offer the kind of abstraction of Rac. In essence we pose a logical choice. That is why we do not offer actual performance metrics. They are not what we are concerned with. Though I will point out that every attempt has been made to optimize Rac. As a general rule we do not talk about internals as I feel this defeats the underlying point of Rac in the first place.
Suffice it to say though Rac is written entirely in t-sql (a system of sp's and a few functions) a solution is arrived at that does not build a query nor system of queries and does not use cursors. It is not a textbook
solution. And we incourage our users to not concern themselves with the whole issue but simply stick to the real issue - solving a problem and not solving a means to a solution. Your comment:
'I've evaluated a lot of software... most of it turns out to be crap one way or another or is nothing more than an expensive replacement for what already exists'
does go to the heart of the issue we are addressing. Rac does not imply nor pretend to do something that could not be done by a very good sql programmer. We are all bound by the same restrictions and limits of
Sql Server. But surely the time and expense of energy required to program complex code could be better spent elsewhere. Rac offers a receipt for that choice.
If I were to pick one specific area of Rac that well drives the situation home it would Racs ability to derive complex ranks. The Sql-99 model of ranking (rank,dense_rank) in S2005, though a welcome addition to abstraction, is rather myopic and simplistic. Interestingly all the hype and reviews has not addressed this. The Rac model of ranking is logically different and allows ranks to be derived not possible in sql-99 and which would be a quite difficult task in t-sql.
The ability to generate such ranks simplies many complex problems. As demonstrated here:
http://racster.blogspot.com/2006/09/rac-are-you-coordinated.html
There are many examples of Rac solutions. You can browse the documentation on the site:
or use 'rac4sql' in a google search.
Let me close with a few caveats. While I am what could best be described as a brilliant programmer
I am certainly not an advocate of 'programming' per se and sql inparticular. Rac is sweet expediency precisely because sql is itself expedient, a poorly designed language giving rise to logically inferior but superior performing database
systems. I am an advocate of Dataphor using the D4 relational language and MS SqlServer as a storage device. I encourage you to visit my blog and see just why.
In fact if you were more interested in the future of your industry than Rac I would be more than satisfied.
best,
steve
-
Wow! Look what happens when I go and leave you guys alone for the weekend!!
In what I consider to be the spirit of these forums, thank you all for your responses and opinions.
While Steve's post may have been pushing the boundaries a little Jeff, I still appreciate his response. In looking at his utility (which I think is very interesting and worthy of further consideration) it allowed me to look at my problem in a different manner and prompted another possible solution ie pivotting the data.
The UDF solution which David has provided is great. In researching the solution I had seen something similar but had discounted it thinking that the UDF had to be a "permanent" object ... that is until I realised I could create and drop it in the reporting stored procedure. The code is brief, simple and pretty easy for those in the know to understand.
I am still tempted to code a pivot solution, just to try it out as I have never done it before. If I were able to code specifically for SQL 2k5 I would use the new pivot command - however that is not possible so I have to think this one through myself.
Thanks again all for even taking the time to reading my post.
Kind regards
Rowan
-
While Steve's post may have been pushing the boundaries a little Jeff, I still appreciate his response. If you're happy, Rowan, then so am I.
Steve, you and your partner seem to have done a nice job according to your own documentation on the RAC site. If nothing else, I appreciate your honesty in the area of abstraction and the possible performance tradeoff (sidebar: Anyone who thinks performance is not important has not had to baby-sit 8 hours of garbage code before being allowed to go home to family and, NO, I'm not insinuating that RAC is garbage, either). So long as some reduction in performance is understood by those interested in the product, I can see it being a great help to those a bit less skilled in SQL (or under the gun) especially since you claim that your underlying code is cursor-less and is likely to have better performance than many home-brewed solutions.
Still, it would be nice to see some dev v.s. run performance metrics in a mega-record environment... intelligent folks will quickly understand the benefit of "canned" methods (best reason is standard methods with already tested functionality) in a RAD environment even if those methods are sometimes slower performance-wise. For similar reasons, it's why some of us will create the occasional UDF even if it may be slower than discreet code...
You may want to approach the owners of SQLServerCentral and see if you can get an "evaluation article" going. It would be a benefit to those needing something like RAC and you wouldn't have to back door your marketing through multiple posts (although you may actually prefer that).
Brilliant programmer?
Maybe, but you and your partner should begin putting at least one space after the period at the end of a sentence in the Rac web site 
Just teasing a bit here, but some folks do look at the attention to detail demonstrated in a web site as a first impression of technical prowess during their product eval. Some are very put off by that type of thing and may not even give the product a chance. Yeah, I know... their loss, but they didn't buy the product, either.In all seriousness, good luck with your product.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Steve... just in case you are interested...
http://www.sqlservercentral.com/community/reviews.asp
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
David gave very simple solution that works perfectly. Thank you!
-
I agree...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hello Jeff,
Your opinions really are most appreciated. Contact me anytime for a free lisence should you wish to piddle
I will do my best to follow up on your suggestions.
best,
Check out RAC at:
www.angelfire.com/ny4/rac/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply