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.idand you'd end up with something like
orders.id | invoices.ids
1 100,101,102
2 103,104,105Of 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_varOtherwise 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: 0mysql> 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 @resultsI 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 :(