View results into string

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » View results into string

11 posts, Page 1 of 2

1 2 »»

View results into string

Rate Topic

Display Mode

Topic Options

Author

Message

jmceuin

Posted Tuesday, March 05, 2013 10:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 07, 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

ryan.mcatee

Posted Tuesday, March 05, 2013 10:29 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 11:33 AM
Points: 43, Visits: 123

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

jmceuin

Posted Tuesday, March 05, 2013 11:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 07, 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

Lowell

Posted Tuesday, March 05, 2013 11:24 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 11,784, Visits: 28,041

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

ryan.mcatee

Posted Tuesday, March 05, 2013 11:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 11:33 AM
Points: 43, Visits: 123

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

opc.three

Posted Tuesday, March 05, 2013 11:37 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 PM
Points: 6,826, Visits: 11,948

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1427182

jmceuin

Posted Thursday, March 07, 2013 1:22 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 07, 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

ryan.mcatee

Posted Thursday, March 07, 2013 1:28 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 11:33 AM
Points: 43, Visits: 123

opc.three (3/5/2013)

That's flipping cool. Thanks for sharing.

Post #1428244

opc.three

Posted Thursday, March 07, 2013 1:31 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 PM
Points: 6,826, Visits: 11,948

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;

Post #1428245

jmceuin

Posted Thursday, March 07, 2013 1:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 07, 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 »

11 posts, Page 1 of 2

1 2 »»

Permissions