MSSQL One-to-Many Aggregation
Posted in Tech
Afternoon watch, 5 bells (2:57 pm)

This post really saved me a lot of time trying to figure out how to aggregate a one-to-many text field in a query on MS SQL server.

set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

OUTPUT:

HeaderValue ChildValues
----------- -------------------
1           CCC
2           AAA, B<&>B
3           <br>, A & Z

(3 row(s) affected)
Leave a Comment »
PHP, PDO, and MSSQL in Windows
Posted in Tech
Last dog watch, 1 bell (6:33 pm)

If, like me, you've been using PHP's PDO via the MSSQL driver, be aware that newer versions of PHP do not support MSSQL natively. Microsoft has it's own PHP driver now, or you can use the FreeTDS MSSQL extension, but I strongly recommend just switching to use the provided ODBC and PDO_ODBC extensions and save yourself the trouble. If you're already using PDOs, then you just have to create the object a little differently. For example:

$sql = new PDO("odbc:Driver={SQL Native Client};Server=ip.add.re.ss;Database=dbname;Uid=user;Pwd=password");

That worked for me. Not sure what you can do about the old mssql_guid_string function if you need that, though. I have the FreeTDS lib installed as well and that lets me call it, until I figure out how to do the same thing in ODBC.

Yes, ASP fans, that's a ONE LINE connection to a database. That's all anyone should need!

Leave a Comment »
I Just Don’t Get It
Posted in Code
First dog watch, 1 bell (4:51 pm)

I'm trying to wrap my head around a nasty MS-SQL one-to-many JOIN problem and I just can't get it to work out.

Basically, I have table a and table b, where a.index = b.index. b can have multiple entries per a.index, but I only care about the first match.

What I want to do is get something like this:
SELECT a.field1, a.field2, b.field
FROM a
JOIN b ON a.index = b.index
WHERE a.field1='this'
...etc...

But for every additional entry in b, I get another row for a. The only field guaranteed to be unique in b is a uniqueid field. It's hard to explain because the whole thing is a complicated mess.

Basically, a is a table of invoices, and b is a table of products added to the invoice. Most of the time, we have one product per invoice, but not always. I only care about seeing the first match. I thought a MIN() or MAX() would get me where I need to be, but it doesn't work—the only thing that's ever different in b is the uniqueid.

It'd be great if you could just JOIN TOP 1 in MSSQL but you can't. Any ideas?

9:05pm Update: I just had a thought about this: how about JOINing to a view that is just a simple SELECT DISTINCT a.uniqueid FROM b? I'll have to try this out tomorrow…

Leave a Comment »
SQL Reporting Services
Posted in Tech
First watch, 5 bells (10:42 pm)

I just spent the last five hours trying to move a Microsoft CRM (version 3) SQL/Reporting Server to a new piece of hardware. Despite having good documentation, it was an incredible chore, and in the end I still ended up having to abort the migration and go back to the old server.

It was really frustrating, because I was 95% of the way there—everything was working except the Reports, and they only weren't visible in the CRM application (if you called them directly, they worked fine. I suspect it has something to do with permissions/trusts between the servers, but I couldn't find it tonight. Plus, I'm just not very smart after working for 14+ hours straight.

Leave a Comment »