Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


View results into string


View results into string

Author
Message
jmceuin
jmceuin
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 10
I have a table where I need to take any number of financial distributions and stack the accounts into a string that can be used as an array.

SELECT POPRCTNM, ACTINDX FROM POP30390 //this would for example return say 3 to 6 rows of data per POPRCTNM each with different ACTINDX numbers and related values (in the database but not requested)

So for example:
RCT0117382 3061
RCT0117382 3061
RCT0117382 46

What I want would be
RCT0117382 3061 3061 46

I then would be able to look at ITEMS on the receipt and if they are of a certain class, check if the expected ACTINDX is in the list used (so I can identify problems from improper human processes).

So, how would I create a table that groups on the PO Receipt Number and has a string field that I can then use crystal to convert to a searchable array for specific account indexes?
ryan.mcatee
ryan.mcatee
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 134
You could try something like this (without having your tables, I couldn't test that this actually works):

SELECT POPRCTNM,
STUFF((SELECT ','+ CAST(ACTINDX AS varchar)
FROM POP30390 a
WHERE a.POPRCTNM = d1.POPRCTNM
GROUP BY a.ACTINDX
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM POP30390 d1


But if your goal is to search for a receipt containing a certain number, you should be able to write this as a query without using the FOR XML hack. What conditions are you looking for that would identify a user-entry error?
jmceuin
jmceuin
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 10
Thanks. That works. But how do I name the column? I added "as ACCTSTRING" to the end of the "STUFF" line and it still shows (no column name) in the results.

As far as what I am doing. Having a view with the receipt and a rolled up field of the account indexes means I can link this to the PO Receipt line item details which lets me determine select which items I need to check and that these items are received with financial distributions into the proper financial accounts. For example, Item XYZ should be received into distribution account index 760. The receipt may have a distribution record with other account indexes as well based on the credits and debits that need to be recorded in the T-account model for accounting. The concern comes when parts of our sales inventoy class are received with no distribution into 760 (or 758 or 759) and thus we have an out of balance issue between what is called our "Subledger" and the "General Ledger". I just had to manually find a $13,000 out of balance error and now having gone through that, want to replicate that in a report that simply highlights those issues to be resolved.

So in Crystal Reports, I will take this and have it show Reciepts where Items of our sales inventory classes have no distribution into accounts 758, 759, or 760. But, I can't just simply say that because other accounts are involved and that gives a bunch of junk and goose chasing. Make sense?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38977
it's not obvious where the alias goes;
here's two examples:

SELECT POPRCTNM,
STUFF((SELECT ','+ CAST(ACTINDX AS varchar)
FROM POP30390 a
WHERE a.POPRCTNM = d1.POPRCTNM
GROUP BY a.ACTINDX
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
AS AliasForStuff
FROM POP30390 d1


SELECT POPRCTNM,
AliasForStuff = STUFF((SELECT ','+ CAST(ACTINDX AS varchar)
FROM POP30390 a
WHERE a.POPRCTNM = d1.POPRCTNM
GROUP BY a.ACTINDX
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')

FROM POP30390 d1


Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ryan.mcatee
ryan.mcatee
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 134
I'm glad it's working and Lowell concisely described how to name the returned attribute.

Still, I would love to see an alternative to this query.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
If you can use the SQLCLR here is an Aggregate Function I wrote to simplify the syntax for these types of queries that performs comparably (sometimes better, sometimes slightly worse) than the XML method you were shown.

GROUP_CONCAT string aggregate for SQL Server

Here is what your query might look like:

SELECT  POPRCTNM,
dbo.GROUP_CONCAT(CAST(ACTINDX AS VARCHAR)) AS AliasForStuff
FROM POP30390 d1
GROUP BY POPRCTNM;



If you wanted a duplicate-free list just add DISTINCT into the Aggregate:

SELECT  POPRCTNM,
dbo.GROUP_CONCAT(DISTINCT CAST(ACTINDX AS VARCHAR)) AS AliasForStuff
FROM POP30390 d1
GROUP BY POPRCTNM;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
jmceuin
jmceuin
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 10
Ok, so the results have duplicates. Is there a way to limit it to distinct? I am getting an error about Group By not working. No details exactly. Also, the GROUP_CONCAT option doesn't work. Apparently that is a MySQL feature not on MS SQL 2008.

I do appreciate the help you all are giving. Just need to now limit to 1 record per POPRCTNM for it to work. I have the rest of my filters working on the Crystal side to compare to the account that should be used.
ryan.mcatee
ryan.mcatee
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 134
opc.three (3/5/2013)
If you can use the SQLCLR here is an Aggregate Function I wrote to simplify the syntax for these types of queries that performs comparably (sometimes better, sometimes slightly worse) than the XML method you were shown...


That's flipping cool. Thanks for sharing.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
jmceuin (3/7/2013)
Also, the GROUP_CONCAT option doesn't work. Apparently that is a MySQL feature not on MS SQL 2008.

Did you read the CodePlex site I linked to? dbo.GROUP_CONCAT() is a SQLCLR object that mimics the functionality offered by the MySQL GROUP_CONCAT() function, but on SQL Server. Before you can use it you have to download the object from the CodePlex site and install it in your database. It's not an exact replacement for what MySQL offers due to some SQLCLR implementation limitations but it is very close. Once installed you can say things like this:

SELECT  POPRCTNM,
dbo.GROUP_CONCAT(DISTINCT CAST(ACTINDX AS VARCHAR)) AS AliasForStuff
FROM POP30390 d1
GROUP BY POPRCTNM;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
jmceuin
jmceuin
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 10
Sorry missed that. And I did more tinkering and just added and additional GROUP BY POPRCTNM to the very end of the statement rather than in the middle and it eliminated the duplicates. The error comes up but still processes anyway. Odd. Anyway. Otherwise, per rules if I don't have to install anything, I don't have to go through a bunch of change control nonsense. Adding a "view" to the database has been ruled as a non-issue for change control because it is not the same as changing a table or data nor installing software. Anyway...

Is there a difference in the performance or load that one method places against the database vs the other?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search