Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

View results into string Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 7, 2013 2:19 PM
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?
Post #1426925
Posted Tuesday, March 5, 2013 10:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:39 AM
Points: 47, Visits: 133
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?
Post #1426933
Posted Tuesday, March 5, 2013 11:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 7, 2013 2:19 PM
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?
Post #1426952
Posted Tuesday, March 5, 2013 11:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 12,903, Visits: 31,972
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1426956
Posted Tuesday, March 5, 2013 11:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:39 AM
Points: 47, Visits: 133
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.
Post #1426958
Posted Tuesday, March 5, 2013 11:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:51 PM
Points: 7,097, Visits: 12,597
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
Post #1427182
Posted Thursday, March 7, 2013 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 7, 2013 2:19 PM
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.
Post #1428240
Posted Thursday, March 7, 2013 1:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:39 AM
Points: 47, Visits: 133
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.
Post #1428244
Posted Thursday, March 7, 2013 1:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:51 PM
Points: 7,097, Visits: 12,597
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
Post #1428245
Posted Thursday, March 7, 2013 1:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 7, 2013 2:19 PM
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?
Post #1428247
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse