Having a brain freeze with a SQL query



  • Okay, this is stumping me for some odd reason:

    I have a table which has invoices that are related to a particular order.  One order can have multiple invoices associated with it.  I want to basically query it to get the information but list each order on a single line, with its associated invoices concatenated into one row (instead of having one row for each order).  But I can't seem to remember how to do that.  I was using Group By, but it didn't seem to be giving the desired results since I need to pull about a dozen columns back, 10 or so of which don't have anything done to them (we just need to know their values), so having them all in the Group By clause seems to throw things off.

    Any advice?  I'm wracking my brain since I know this can be done. 



  •  Have you tried:

    (CASE WHEN "Help Desk Attributes".Priority = '3-Medium' AND ("Help Desk Attributes"."TAT To Close - Days" - (Case WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" = 0 THEN 0 WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" <= 5 THEN 1 WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" = 6 THEN 2 WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" <= 12 THEN 3 WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" = 13 THEN 4 WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" <= 19 THEN 5 WHEN "- Open Date"."Day in Week" = 2 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 4 THEN 0 WHEN "- Open Date"."Day in Week" = 2 AND "Help Desk Attributes"."TAT To Close - Days" = 5 THEN 1 WHEN "- Open Date"."Day in Week" = 2 AND "Help Desk Attributes"."TAT To Close - Days" <= 11 THEN 2 WHEN "- Open Date"."Day in Week" = 2 AND "Help Desk Attributes"."TAT To Close - Days" = 12 THEN 3 WHEN "- Open Date"."Day in Week" = 2 AND "Help Desk Attributes"."TAT To Close - Days" <= 18 THEN 4 WHEN "- Open Date"."Day in Week" = 3 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 3 THEN 0 WHEN "- Open Date"."Day in Week" = 3 AND "Help Desk Attributes"."TAT To Close - Days" = 4 THEN 1 WHEN "- Open Date"."Day in Week" = 3 AND "Help Desk Attributes"."TAT To Close - Days" <= 10 THEN 2 WHEN "- Open Date"."Day in Week" = 3 AND "Help Desk Attributes"."TAT To Close - Days" = 11 THEN 3 WHEN "- Open Date"."Day in Week" = 3 AND "Help Desk Attributes"."TAT To Close - Days" <= 17 THEN 4 WHEN "- Open Date"."Day in Week" = 4 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 2 THEN 0 WHEN "- Open Date"."Day in Week" = 4 AND "Help Desk Attributes"."TAT To Close - Days" = 3 THEN 1 WHEN "- Open Date"."Day in Week" = 4 AND "Help Desk Attributes"."TAT To Close - Days" <= 9 THEN 2 WHEN "- Open Date"."Day in Week" = 4 AND "Help Desk Attributes"."TAT To Close - Days" = 10 THEN 3 WHEN "- Open Date"."Day in Week" = 4 AND "Help Desk Attributes"."TAT To Close - Days" <= 16 THEN 4 WHEN "- Open Date"."Day in Week" = 5 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 1 THEN 0 WHEN "- Open Date"."Day in Week" = 5 AND "Help Desk Attributes"."TAT To Close - Days" = 2 THEN 1 WHEN "- Open Date"."Day in Week" = 5 AND "Help Desk Attributes"."TAT To Close - Days" <= 8 THEN 2 WHEN "- Open Date"."Day in Week" = 5 AND "Help Desk Attributes"."TAT To Close - Days" = 9 THEN 3 WHEN "- Open Date"."Day in Week" = 5 AND "Help Desk Attributes"."TAT To Close - Days" <= 15 THEN 4 WHEN "- Open Date"."Day in Week" = 6 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 0 THEN 0 WHEN "- Open Date"."Day in Week" = 6 AND "Help Desk Attributes"."TAT To Close - Days" = 1 THEN 1 WHEN "- Open Date"."Day in Week" = 6 AND "Help Desk Attributes"."TAT To Close - Days" <= 7 THEN 2 WHEN "- Open Date"."Day in Week" = 6 AND "Help Desk Attributes"."TAT To Close - Days" = 8 THEN 3 WHEN "- Open Date"."Day in Week" = 6 AND "Help Desk Attributes"."TAT To Close - Days" <= 14 THEN 4 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 0 THEN 0 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" = 1 THEN 1 WHEN "- Open Date"."Day in Week" <= 7 AND "Help Desk Attributes"."TAT To Close - Days" <= 76 THEN 2 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" = 7 THEN 3 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" <= 13 THEN 4 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" = 14 THEN 5 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" <= 20 THEN 6 ELSE 0 END)) <= 5 THEN 1 WHEN "Help Desk Attributes".Priority = '4-Low' AND ("Help Desk Attributes"."TAT To Close - Days" - (Case WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" = 0 THEN 0 WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" <= 5 THEN 1 WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" = 6 THEN 2 WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" <= 12 THEN 3 WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" = 13 THEN 4 WHEN "- Open Date"."Day in Week" = 1 AND "Help Desk Attributes"."TAT To Close - Days" <= 19 THEN 5 WHEN "- Open Date"."Day in Week" = 2 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 4 THEN 0 WHEN "- Open Date"."Day in Week" = 2 AND "Help Desk Attributes"."TAT To Close - Days" = 5 THEN 1 WHEN "- Open Date"."Day in Week" = 2 AND "Help Desk Attributes"."TAT To Close - Days" <= 11 THEN 2 WHEN "- Open Date"."Day in Week" = 2 AND "Help Desk Attributes"."TAT To Close - Days" = 12 THEN 3 WHEN "- Open Date"."Day in Week" = 2 AND "Help Desk Attributes"."TAT To Close - Days" <= 18 THEN 4 WHEN "- Open Date"."Day in Week" = 3 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 3 THEN 0 WHEN "- Open Date"."Day in Week" = 3 AND "Help Desk Attributes"."TAT To Close - Days" = 4 THEN 1 WHEN "- Open Date"."Day in Week" = 3 AND "Help Desk Attributes"."TAT To Close - Days" <= 10 THEN 2 WHEN "- Open Date"."Day in Week" = 3 AND "Help Desk Attributes"."TAT To Close - Days" = 11 THEN 3 WHEN "- Open Date"."Day in Week" = 3 AND "Help Desk Attributes"."TAT To Close - Days" <= 17 THEN 4 WHEN "- Open Date"."Day in Week" = 4 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 2 THEN 0 WHEN "- Open Date"."Day in Week" = 4 AND "Help Desk Attributes"."TAT To Close - Days" = 3 THEN 1 WHEN "- Open Date"."Day in Week" = 4 AND "Help Desk Attributes"."TAT To Close - Days" <= 9 THEN 2 WHEN "- Open Date"."Day in Week" = 4 AND "Help Desk Attributes"."TAT To Close - Days" = 10 THEN 3 WHEN "- Open Date"."Day in Week" = 4 AND "Help Desk Attributes"."TAT To Close - Days" <= 16 THEN 4 WHEN "- Open Date"."Day in Week" = 5 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 1 THEN 0 WHEN "- Open Date"."Day in Week" = 5 AND "Help Desk Attributes"."TAT To Close - Days" = 2 THEN 1 WHEN "- Open Date"."Day in Week" = 5 AND "Help Desk Attributes"."TAT To Close - Days" <= 8 THEN 2 WHEN "- Open Date"."Day in Week" = 5 AND "Help Desk Attributes"."TAT To Close - Days" = 9 THEN 3 WHEN "- Open Date"."Day in Week" = 5 AND "Help Desk Attributes"."TAT To Close - Days" <= 15 THEN 4 WHEN "- Open Date"."Day in Week" = 6 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 0 THEN 0 WHEN "- Open Date"."Day in Week" = 6 AND "Help Desk Attributes"."TAT To Close - Days" = 1 THEN 1 WHEN "- Open Date"."Day in Week" = 6 AND "Help Desk Attributes"."TAT To Close - Days" <= 7 THEN 2 WHEN "- Open Date"."Day in Week" = 6 AND "Help Desk Attributes"."TAT To Close - Days" = 8 THEN 3 WHEN "- Open Date"."Day in Week" = 6 AND "Help Desk Attributes"."TAT To Close - Days" <= 14 THEN 4 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" BETWEEN -1 AND 0 THEN 0 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" = 1 THEN 1 WHEN "- Open Date"."Day in Week" <= 7 AND "Help Desk Attributes"."TAT To Close - Days" <= 76 THEN 2 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" = 7 THEN 3 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" <= 13 THEN 4 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" = 14 THEN 5 WHEN "- Open Date"."Day in Week" = 7 AND "Help Desk Attributes"."TAT To Close - Days" <= 20 THEN 6 ELSE 0 END)) <= 10 THEN 1 ELSE 0 END)

     

    ...Sorry I couldn't resist.



  • My eyes, the goggles do nothing!

    X_X 



  • @ObiWayneKenobi said:

    I want to basically query it to get the information but list each order on a single line, with its associated invoices concatenated into one row (instead of having one row for each order).
     

    I'm not sure if this is possible. How would the data be returned?

    For example, ignoring the orders table for the moment, if you had a table invoices: id | order_id | date_sent | etc

    Then how would you return multiple rows from that table in a single row in any meaningful way? 



  • You know, I really don't know.  I'm just trying to do my job :) and the issue is that we have orders that can have many invoices, and my boss wants a list of them like that. I could do it in code, I guess, instead of SQL, and just loop over the value.



  • @ObiWayneKenobi said:

    You know, I really don't know.  I'm just trying to do my job :) and the issue is that we have orders that can have many invoices, and my boss wants a list of them like that. I could do it in code, I guess, instead of SQL, and just loop over the value.

     

    Not a SQL expert, so I don't have a solution, but it sounds like it would be easier done outside of SQL to me.

    This would probably be good application for a nice treeview.

    Thats my vote.



  • I wanted to do this same thing, where I was asked to combine related values into one row of output.  I found a way to do it in Oracle:

     

     http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

     

    If you're not using Oracle, you might be able to use a similar thing.

     

    I ended up not using these esoteric methods, and just used program code to massage the SQL results. 



  •  There is no SQL-only solution, as far as I know (and I've done quite a lot of SQL programming over the past few years). The way to go is either the Stored-Procedures way, as sinistral is suggesting, or the programming way, as MasterPlanSoftware suggested.



  •  MySQL has a GROUP_CONCAT function for things like this:

    SELECT orders.id, GROUP_CONCAT(invoices.id)
    FROM orders
    LEFT JOIN invoices ON orders.id = invoices.orderid
    GROUP BY orders.id

    and you'd end up with something like

    orders.id   | invoices.ids
    1                100,101,102
    2                103,104,105

    Of course, you can concatenate any of the query's fields you want, not just numeric fields. There's extra options for sorting and specifying seperator strings as well. 



  • Am I the only one who wonders why this has not been addressed by a standard function in all major database systems yet? This kind of requirement is not exactly uncommon...



  •  @ammoQ said:

    Am I the only one who wonders why this has not been addressed by a standard function in all major database systems yet? This kind of requirement is not exactly uncommon...

    I'm assuming you are talking about a similiar function to MySQLs' GROUP_CONCAT.  I haven't ever needed that specific functionality. 

    I haven't really thought about it, but could a common table expression be used to do the concatenation?  



  • Well I don't know about other DBMSs, but in Sybase, you can (surprisingly) do update on the table, appending to a variable. In fact it doesn't update the table at all, but it does concatenate into the variable. No way of ordering though. Try something like this:

    declare @long_var varchar(3000)
    select @long_var = ""
    update my_table
    set @long_var = @long_var + ', '+ my_column
    from my_table
    select @long_var

    Otherwise you need some sort of procedural loop, or do it in the front end.



  • There is no way to do this in standard SQL without a Cursor if there is no limit to the number of Invoices that can be attached to an Order.  

     Select ((Select Invoice from Inoices I inner join Invoices I1 on I on I.Order = I1.Order group by OrderNo having count(case when I.InvoiceNo >= I1.InvoiceNo then InvoiceNo else NUll End))) = 1) + ', ' +)

    ((Select Invoice from Inoices I inner join Invoices I1 on I on I.Order = I1.Order group by OrderNo having count(case when I.InvoiceNo >= I1.InvoiceNo then InvoiceNo else NUll End))) = 2) +', ' +)

    ((Select Invoice from Inoices I inner join Invoices I1 on I on I.Order = I1.Order group by OrderNo having count(case when I.InvoiceNo >= I1.InvoiceNo then InvoiceNo else NUll End))) = 3) +', ' +)

    ((Select Invoice from Inoices I inner join Invoices I1 on I on I.Order = I1.Order group by OrderNo having count(case when I.InvoiceNo >= I1.InvoiceNo then InvoiceNo else NUll End))) = 4) +', ' +)

    ((Select Invoice from Inoices I inner join Invoices I1 on I on I.Order = I1.Order group by OrderNo having count(case when I.InvoiceNo >= I1.InvoiceNo then InvoiceNo else NUll End))) = 5) +', ' +)

    ((Select Invoice from Inoices I inner join Invoices I1 on I on I.Order = I1.Order group by OrderNo having count(case when I.InvoiceNo >= I1.InvoiceNo then InvoiceNo else NUll End))) = 6) +', ' +)

    ((Select Invoice from Inoices I inner join Invoices I1 on I on I.Order = I1.Order group by OrderNo having count(case when I.InvoiceNo >= I1.InvoiceNo then InvoiceNo else NUll End))) = 7) +', ' +)

    ((Select Invoice from Inoices I inner join Invoices I1 on I on I.Order = I1.Order group by OrderNo having count(case when I.InvoiceNo >= I1.InvoiceNo then InvoiceNo else NUll End))) = 8) +', ' +)

    ((Select Invoice from Inoices I inner join Invoices I1 on I on I.Order = I1.Order group by OrderNo having count(case when I.InvoiceNo >= I1.InvoiceNo then InvoiceNo else NUll End))) = 9)))

     from Order where Order = OOO9999 

     

    For prodution use, you should check each of the above for being NULL



  • @vr602 said:

    Well I don't know about other DBMSs, but in Sybase, you can (surprisingly) do update on the table, appending to a variable
     

    There's a semi-cute/hackish way of doing it in MySQL as well without using the GROUP_CONCAT function, though it's not nearly as versatile:

    mysql> create table test (x char(1));
    Query OK, 0 rows affected (0.01 sec)
    mysql> insert into test (x) values ('a'), ('b'), ('c'), ('d');
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    mysql> set @blah:='';
    Query OK, 0 rows affected (0.00 sec)

    mysql> select @blah:=concat(@blah, ' ', x) AS blah from test;
    +--------------+
    | blah         |
    +--------------+
    |  a           |
    |  a b         |
    |  a b c      |
    |  a b c d     |
    +--------------+
    4 rows in set (0.00 sec)

    On occasion, I supposed this behavior could come in handy, if you don't mind the client-side processing to ignore the query results and re-select just the @blah variable again, or ignore all of the returned rows except the ones matching your grouping requirements.



  • You can do this in SQL Server, but there are no native functions to do what you want.  I have a system here that has a similar relationship to what you are dealing with and put together the following that accomplishes what you want.  My system is relating timesheets to invoices, whereas yours is invoices to orders.  In my system one invoice can have multiple timesheets associated with it.  One of the suggestions was to use program code and that's essentially what I've done here, but done procedural code in SQL Server:

    -- table to hold results
    DECLARE @results TABLE
    (
     InvoiceNumber int,
     timesheets varchar(MAX)
    )
    -- temp table for working with raw data
    DECLARE @temp1 TABLE
    (
     InvoiceNumber int,
     timesheet_num int,
     group_num int,
     item_num int
    )
    -- pull raw data into temp table
    insert @temp1
    select HEAD.InvoiceNumber, DET.timesheet_num, GRPS.group_num, ROW_NUMBER() OVER(PARTITION BY HEAD.InvoiceNumber ORDER BY HEAD.InvoiceNumber, DET.timesheet_num) [item_num]
      from Invoicing.tblInvoiceHeader HEAD (nolock)
     inner join (select distinct InvoiceNumber, ROW_NUMBER() OVER(ORDER BY InvoiceNumber) [group_num] from Invoicing.tblInvoiceHeader (nolock)) GRPS on GRPS.InvoiceNumber = HEAD.InvoiceNumber
     inner join Invoicing.tblSubInvoice SUB (nolock) on SUB.InvIDKey = HEAD.IDKey
     inner join Invoicing.tblInvoiceDetails DET (nolock) on DET.SubInvIDKey = SUB.IDKey
     where DET.SourceTypeIDKEy = 1 AND SUB.InvoiceStatus = 1
     group by HEAD.InvoiceNumber, DET.timesheet_num, GRPS.group_num
     order by HEAD.InvoiceNumber, DET.timesheet_num

    -- variables to control loops
    DECLARE @groupNum int
    DECLARE @maxGroupNum int
    DECLARE @itemNum int
    DECLARE @maxItemNum int
    DECLARE @invoiceNum int
    DECLARE @timesheetNum varchar(10)
    SELECT @groupNum = 1, @maxGroupNum = (select max(group_num) from @temp1)
    WHILE @groupNum <= @maxGroupNum
    BEGIN
     -- get the invoice number of this group
     SELECT @invoiceNum = (select distinct InvoiceNumber from @temp1 where group_num = @groupNum)
     -- get max item num for the current group and initialize counter
     SELECT @maxItemNum = (select max(item_num) from @temp1 where group_num = @groupNum), @itemNum = 1
     -- loop over the items within the current group and concatenate the value into the results table
     WHILE @itemNum <= @maxItemNum
     BEGIN
      SELECT @timesheetNum = CAST((select timesheet_num from @temp1 where group_num = @groupNum and item_num = @itemNum) as varchar(10))
      -- for first entry, insert new record; otherwise update existing record
      IF @itemNum = 1
       INSERT @results (InvoiceNumber, timesheets) VALUES (@invoiceNum, @timesheetNum)
      ELSE
       UPDATE @results SET timesheets = timesheets + ',' + @timesheetNum
      -- increment item counter
      SELECT @itemNum = @itemNum + 1
     END
     -- increment group counter
     SELECT @groupNum = @groupNum + 1
    END
    select * from @results

    I apologize if the formatting is all screwy here, but the main idea is using the ROW_NUMBER() function to assign a group number and an item number to each item.  You'll see that I assign the group number by joining to a sub query where I use the ROW_NUMBER function; this is because I was having problems doing it in the main query and was content with this alternative as it has the desired effect.  Then, there are two loops that iterate over the items and construct a result string of comma-separated values.  I've created this and it runs against SQL Server 2005, but it may not work against other versions.



  • Hi There,

     There is a set-based way of creating CSVs in this way, using scalar UDFs. It's a lot faster, and takes less toll on SQL Server. Here's an example using a store table, product table, and store-product table for the relationship:

     1: Tables and their data:

    <FONT color=#008000 size=2></FONT><FONT color=#008000 size=2>

    /****** Object: Table [dbo].[Stores] Script Date: 04/30/2008 20:55:43 ******/

    </FONT><FONT color=#0000ff size=2>

    SET</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ANSI_NULLS</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ON

    </FONT><FONT size=2>

    GO

    </FONT><FONT color=#0000ff size=2>

    SET</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>QUOTED_IDENTIFIER</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ON

    </FONT><FONT size=2>

    GO

    </FONT><FONT color=#0000ff size=2>

    CREATE</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>TABLE</FONT><FONT size=2> [dbo]</FONT><FONT color=#808080 size=2>.</FONT><FONT size=2>[Stores]</FONT><FONT color=#808080 size=2>(

    </FONT><FONT size=2>

    [ID] [int] </FONT><FONT color=#0000ff size=2>IDENTITY</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>1</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>1</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>NOT</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>NULL,

    </FONT><FONT size=2>[Store] [nvarchar]</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>50</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>COLLATE</FONT><FONT size=2> Latin1_General_CI_AS </FONT><FONT color=#808080 size=2>NOT</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>NULL</FONT></FONT><FONT color=#808080 size=2>

    )</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ON</FONT><FONT size=2> [PRIMARY]

    </FONT><FONT color=#008000 size=2>

    /****** Object: Table [dbo].[Products] Script Date: 04/30/2008 20:56:01 ******/

    </FONT><FONT color=#0000ff size=2>

    SET</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ANSI_NULLS</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ON

    </FONT><FONT size=2>

    GO

    </FONT><FONT color=#0000ff size=2>

    SET</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>QUOTED_IDENTIFIER</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ON

    </FONT><FONT size=2>

    GO

    </FONT><FONT color=#0000ff size=2>

    CREATE</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>TABLE</FONT><FONT size=2> [dbo]</FONT><FONT color=#808080 size=2>.</FONT><FONT size=2>[Products]</FONT><FONT color=#808080 size=2>(

    </FONT><FONT size=2>

    [ID] [int] </FONT><FONT color=#0000ff size=2>IDENTITY</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>1</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>1</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>NOT</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>NULL,

    </FONT><FONT size=2>[Product] [nvarchar]</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>50</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>COLLATE</FONT><FONT size=2> Latin1_General_CI_AS </FONT><FONT color=#808080 size=2>NOT</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>NULL</FONT></FONT><FONT color=#808080 size=2>

    )</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ON</FONT><FONT size=2> [PRIMARY]

    </FONT><FONT color=#008000 size=2>

    /****** Object: Table [dbo].[StoreProducts] Script Date: 04/30/2008 20:56:14 ******/

    </FONT><FONT color=#0000ff size=2>

    SET</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ANSI_NULLS</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ON

    </FONT><FONT size=2>

    GO

    </FONT><FONT color=#0000ff size=2>

    SET</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>QUOTED_IDENTIFIER</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ON

    </FONT><FONT size=2>

    GO

    </FONT><FONT color=#0000ff size=2>

    CREATE</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>TABLE</FONT><FONT size=2> [dbo]</FONT><FONT color=#808080 size=2>.</FONT><FONT size=2>[StoreProducts]</FONT><FONT color=#808080 size=2>(

    </FONT><FONT size=2>

    [ID] [int] </FONT><FONT color=#0000ff size=2>IDENTITY</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>1</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>1</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>NOT</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>NULL,

    </FONT><FONT size=2>

    [StoreID] [int] </FONT><FONT color=#808080 size=2>NOT</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>NULL,

    </FONT><FONT size=2>[ProductID] [int] </FONT><FONT color=#808080 size=2>NOT</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>NULL</FONT></FONT><FONT color=#808080 size=2>

    )</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ON</FONT><FONT size=2> [PRIMARY]

    GO

    </FONT><FONT color=#0000ff size=2>

    INSERT</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>INTO</FONT><FONT size=2> Stores</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>Store</FONT><FONT color=#808080 size=2>)

    </FONT><FONT color=#0000ff size=2>SELECT</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'Norwich'</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>UNION</FONT></FONT><FONT color=#0000ff size=2>

    SELECT</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'Yarmouth'</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>UNION

    SELECT</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'London'

    </FONT><FONT size=2>

    GO

    </FONT><FONT color=#0000ff size=2>

    INSERT</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>INTO</FONT><FONT size=2> Products</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>Product</FONT><FONT color=#808080 size=2>)

    </FONT><FONT color=#0000ff size=2>SELECT</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'Book'</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>UNION</FONT></FONT><FONT color=#0000ff size=2>

    SELECT</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'CD'</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>UNION

    SELECT</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'DVD'

    </FONT><FONT size=2>

    GO

    </FONT><FONT color=#0000ff size=2>

    INSERT</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>INTO</FONT><FONT size=2> StoreProducts</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>StoreID</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> ProductID</FONT><FONT color=#808080 size=2>)

    </FONT><FONT color=#0000ff size=2>SELECT</FONT><FONT size=2> 1</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> 1 </FONT><FONT color=#0000ff size=2>UNION</FONT></FONT><FONT color=#0000ff size=2>

    SELECT</FONT><FONT size=2> 1</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> 2 </FONT><FONT color=#0000ff size=2>UNION

    SELECT</FONT><FONT size=2> 1</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> 3 </FONT><FONT color=#0000ff size=2>UNION</FONT></FONT><FONT color=#0000ff size=2>

    SELECT</FONT><FONT size=2> 2</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> 2 </FONT><FONT color=#0000ff size=2>UNION

    SELECT</FONT><FONT size=2> 2</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> 3 </FONT><FONT color=#0000ff size=2>UNION</FONT></FONT><FONT color=#0000ff size=2>

    SELECT</FONT><FONT size=2> 3</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> 1 </FONT><FONT color=#0000ff size=2>UNION

    SELECT</FONT><FONT size=2> 3</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> 2</FONT></FONT><FONT size=2>

    GO

     

    2: The scalar UDF:

    <FONT color=#0000ff size=2>

    CREATE</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>FUNCTION</FONT><FONT size=2> MakeProductCSV

    </FONT><FONT color=#808080 size=2>

    (

    </FONT><FONT size=2>

    </FONT><FONT color=#008000 size=2>-- Add the parameters for the function here

    </FONT><FONT size=2>

    @StoreID </FONT><FONT color=#0000ff size=2>INT

    </FONT><FONT color=#808080 size=2>

    )

    </FONT><FONT color=#0000ff size=2>

    RETURNS</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>NVARCHAR</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>1000</FONT><FONT color=#808080 size=2>)

    </FONT><FONT color=#0000ff size=2>

    AS

    BEGIN

    </FONT><FONT size=2>

    </FONT><FONT color=#008000 size=2>-- Declare the return variable here

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>DECLARE</FONT><FONT size=2> @Output </FONT><FONT color=#0000ff size=2>NVARCHAR</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>1000</FONT><FONT color=#808080 size=2>)

    </FONT><FONT size=2>

    </FONT><FONT color=#008000 size=2>-- Add the T-SQL statements to compute the return value here

    </FONT><FONT size=2></FONT><FONT color=#0000ff size=2>SELECT</FONT><FONT size=2> @Output </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>ISNULL</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>@Output </FONT><FONT color=#808080 size=2>+</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>', '</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>''</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>+</FONT><FONT size=2> Products</FONT><FONT color=#808080 size=2>.</FONT><FONT size=2>Product</FONT></FONT><FONT size=2></FONT><FONT color=#0000ff size=2>FROM</FONT><FONT size=2> </FONT></FONT><FONT size=2>

    Stores

    </FONT><FONT color=#808080 size=2>INNER</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>JOIN</FONT><FONT size=2> StoreProducts </FONT><FONT color=#0000ff size=2>ON</FONT><FONT size=2> Stores</FONT><FONT color=#808080 size=2>.</FONT><FONT size=2>ID </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> StoreID

    </FONT><FONT color=#808080 size=2>INNER</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>JOIN</FONT><FONT size=2> Products </FONT><FONT color=#0000ff size=2>ON</FONT><FONT size=2> Products</FONT><FONT color=#808080 size=2>.</FONT><FONT size=2>ID </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> ProductID</FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>WHERE

    </FONT><FONT size=2>StoreID </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> @StoreID</FONT></FONT><FONT size=2>

    </FONT><FONT color=#008000 size=2>-- Return the result of the function

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>RETURN</FONT><FONT size=2> @Output

    </FONT><FONT color=#0000ff size=2>

    END

    </FONT><FONT size=2>

    GO

    </FONT>

     

    3: The final query:

    <FONT color=#0000ff size=2>

    SELECT

    </FONT><FONT size=2>

    Stores</FONT><FONT color=#808080 size=2>.</FONT><FONT size=2>Store</FONT><FONT color=#808080 size=2>,

    </FONT><FONT size=2>

    dbo</FONT><FONT color=#808080 size=2>.</FONT><FONT size=2>MakeProductCSV</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>Stores</FONT><FONT color=#808080 size=2>.</FONT><FONT size=2>ID</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>AS</FONT><FONT size=2> ProductsCSV

    </FONT><FONT color=#0000ff size=2>

    FROM

    </FONT><FONT size=2>

    Stores

    </FONT>

     

    I inherited a function like this from my predecessor, using a cursor in a scalar UDF to make CSVs, one for each row in a large grid on a busy page. Replacing it with the set based solution saved a ridiculous amount of time :D

    </FONT>

     



  • Oh, and with the above, like an earlier post, check for null in the UDF on Products.Product. COALESCE(Products.Product, '') should be fine. Good luck!



  •  You don't think he might have figured that out in the last 22 days?



  • @MasterPlanSoftware said:

     You don't think he might have figured that out in the last 22 days?

    He might have, but the people who said it was impossible probably won't have. Also I didn't realise the date, apologies for the necromancy :(


Log in to reply