SUM(Varchar Column). Is this Possible?
-
Hi.....,
To one of our sql server database Table A, we need to migrate data from another sql server database Table B. Assume there are two columns in a table namely OrderID and Comments. Here OrderID is a foreign key. And therefore, I can have any number of comments for a Particular OrderID. Thru a TSQL query, I wish to have all the Comments data beside the OrderID seperated by comma like
Ord1 Comm1, Comm2, Comm3
Ord2 Comm5, Comm6. Comm7, Comm8, etc.,
This is just the thing we used to do for numeric columns using GROUP BY statement as
SELECT ORDERID, SUM(ORDER_TOTAL) AS AMT FROM B GROUP BY ORDERID
Want to know how to achieve similar effect with Varchar type of data. I'm currently using Cursor to concat, but it is taking long long time...coz i have around 140000+ ORDERID's to migrate and each ORDERID has a minimum of 4 and to a maximum of 12 rows of comments.
Thanx and Regards,
Hemant
-
Your going about it the best way you will find in SQL Server 7 and 2000 however if 2005 try PIVOT
see http://msdn2.microsoft.com/en-us/library/ms177410.aspx
Otherwise I would normally suggest it best to preform this logic in your presentation layer than in the data layer.
-
You can definitly concatenate rows in a select statement. Give me a few mins and I'll give you an example
SQL guy and Houston Magician
-
/**************************************** Set up example table structure ***
*************************************/
CREATE TABLE #order
(
Order_id INT PRIMARY KEY CLUSTERED
-- ...
)
CREATE TABLE #OrderComment
(
CommentID INT PRIMARY KEY NONCLUSTERED,
Order_ID INT REFERENCES #order(Order_Id),
Comment VARCHAR(500)
-- ...
)
CREATE CLUSTERED INDEX IXC__OrderComment__Order_Id -- I already know not to name indexes on temp tables.
ON #OrderComment(Order_ID) ON [PRIMARY]
/*************************************
*** Populate example tables ***
*************************************/
INSERT INTO #Order(Order_ID)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
INSERT INTO #Ordercomment(CommentID, Order_ID, Comment)
SELECT 1, 1, 'Comment1'
UNION ALL
SELECT 2, 1, 'another comment'
UNION ALL
SELECT 3, 1, 'other thigns to say'
UNION ALL
SELECT 4, 1, 'this order was shipped priority'
UNION ALL
SELECT 5, 2, 'this order was not'
UNION ALL
SELECT 6, 2, 'this order went to market'
UNION ALL
SELECT 7, 2, 'this order stayed home'
UNION ALL
SELECT 8, 2, 'this one had roast beef'
UNION ALL
SELECT 9, 2, '....'
UNION ALL
SELECT 10, 2, 'Pick a card, any card'
UNION ALL
SELECT 11, 2, '...'
UNION ALL
SELECT 12, 3, 'so you get the idea'
UNION ALL
SELECT 13, 3, 'Comment'
UNION ALL
SELECT 14, 3, 'more Comments'
UNION ALL
SELECT 15, 3, 'even more Comments'
UNION ALL
SELECT 16, 3, 'etc'
/*************************************
*** Example code ***
*************************************/
SELECT Order_ID, --assuming there are at min 4 and max 12 comments as per the post
CAST(COALESCE(MIN(CASE WHEN NUM = 1 THEN Comment END),'') AS VARCHAR(MAX)) +
CAST(COALESCE(MIN(CASE WHEN NUM = 2 THEN Comment END),'') AS VARCHAR(MAX)) +
CAST(COALESCE(MIN(CASE WHEN NUM = 3 THEN Comment END),'') AS VARCHAR(MAX)) +
CAST(COALESCE(MIN(CASE WHEN NUM = 4 THEN Comment END),'') AS VARCHAR(MAX)) +
CAST(COALESCE(MIN(CASE WHEN NUM = 5 THEN Comment END),'') AS VARCHAR(MAX)) +
CAST(COALESCE(MIN(CASE WHEN NUM = 6 THEN Comment END),'') AS VARCHAR(MAX)) +
CAST(COALESCE(MIN(CASE WHEN NUM = 7 THEN Comment END),'') AS VARCHAR(MAX)) +
CAST(COALESCE(MIN(CASE WHEN NUM = 8 THEN Comment END),'') AS VARCHAR(MAX)) +
CAST(COALESCE(MIN(CASE WHEN NUM = 9 THEN Comment END),'') AS VARCHAR(MAX)) +
CAST(COALESCE(MIN(CASE WHEN NUM = 0 THEN Comment END),'') AS VARCHAR(MAX)) +
CAST(COALESCE(MIN(CASE WHEN NUM = 11 THEN Comment END),'') AS VARCHAR(MAX)) +
CAST(COALESCE(MIN(CASE WHEN NUM = 12 THEN Comment END),'') AS VARCHAR(MAX)) AS Comment
FROM
(
SELECT O.Order_ID, C.Comment + ',' AS Comment,
ROW_NUMBER() OVER (PARTITION BY C.Order_Id ORDER BY CommentId) AS Num
FROM #Order O
INNER JOIN #OrderComment C
ON O.Order_Id = C.Order_Id
) OD
GROUP BY OD.Order_Id
/*************************************
*** clean up ***
*************************************/
DROP TABLE #Order
DROP TABLE #OrderComment
SQL guy and Houston Magician
-
Consider a function, like below...
CREATE FUNCTION myConcantenate
(@Id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @RetStr varchar(8000)
SELECT @RetStr = COALESCE(@RetStr + ', ', '') + SomeTable.SomeCharColumn
FROM SomeTable
WHERE SomeTableId = @Id
RETURN @RetStr
END
GO
Then your select would look like this...
SELECT SomeParentTable.SomeParentTableId, dbo.myConcantenate(SomeParentTableId)
FROM SomeParentTable
WHERE SomeParentTableId...
Good luck!
-
Hello Robert Cary and Antares686,
Thanx for looking into the issue. Unfortunately guys, I'm in need of a T-SQL in 2000 to achieve the solution. Hope i would have been asked to go with 2005
Hello John Beggs,
Currently i have adapted your method (using function call from the select statement) to perform the migration and as of now this is quicker than cursor. Cursor took around 11 secs to insert just 10 records and the method which you have suggested took 4 secs to insert 10 records. I 'm happy to see some performance gain and also understand that inserting just 10 records in 4 secs is too much. Any ways i will be expecting a much quicker query.
Thanx & Regards guys,
Hemant
-
What is the max width of your comment column? Could you please provide the schema for your two tables?
Thanks
SQL guy and Houston Magician
-
Glad that is working for you Hemant. I must be honest though, I am suprised that inserting 10 records would take that long.
I would verify that you have appropriate indexing to support the searches involved here.
-
Hello Robert Cary,
The max width of the comment column is 75 and the following is the schema of the two tables
tblOrderComments (table in source db) - no indexing in this table
OrderID Varchar(13)
OrderComment Varchar(75)
tblOrders (table in dest db)
OrderID BigInt (Primary Key)
Comments Varchar(500)
.....
Hello John Beggs,
First off, the real database is actually a text file. What we did is first we migrated the data to a temp sql server database. In the text file, there were no hints about the indexed column. So, we didn't have any index on the temp database tables (and now i realize that this is one of the factor for the performance). Then we executed the query on the temp database to migarte to our real database.
-
I'm not sure i fully follow, but it sounds like you wish to summarize order information, but include detailed comments, correct?
I agree with the previous post - a function to produce the output is your best answer. I would implement it at run-time dynamically instead of physically including comments in the aggregate table.
When needed, use something like this:
Select *, dbo.udfOrderComments(OrderID) from AggregateTable .....
This will avoid having to store duplicate information in the summarized table.
-
Hello David Wasy,
Thank you for your interest in looking into my problem and helping to solve that. Actually you can see from my previous posts that i 'm migrating the data from a temp database to our real database. Though this is a one time process, it takes a lot of time migrating the comments data alone. There are many rows of comments for each order id and in our real database we want all the comments in a single row for each order id. So trying to concatinate all the comments for the given order id is a question with better performance. Hope this will be more clearer.
Thanx again,
Hemant
-
I do follow ... now
I think i would create the initial table using the group-by logic...
then make a second pass for comments - which i'll have to sleep on... getting late
btw: you might want to consider a table for comments.. but that's just a tired tought... g'nite
-
CREATE TABLE [OrderComments] (
[OrderID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-- populate table
create function ronald.fn_ConCatComments(@Orderid char(5))
returns varchar(8000)
as
begin
declare @Comments varchar(8000)
set @Comments = ''
select @Comments = @Comments + comments + '_' from ordercomments where orderid = @orderid
return left(@Comments, len(@Comments) -1)
end
Select distinct OrderID, ronald.fn_ConCatComments(Orderid)
from OrderComments
-- Replace datatypes with the appropriate ones.
-
that gets the data, but remember that this is coming from a temporary set of tables or database to a new final database. He needs to populate the comments into a new, permanent table.
..and several people layed out that function but performance was the issue.
BTW, you can avoid the extra "_" delimiter at the end by NOT initiallizing the @comment variable too...
Select
@Comment = isnull(@Comment + '_' , '') + Comments from OrderComments where......
My gut says make the comments a new permanent table and use the function at run-time to build up the list when needed. This would give you a better history of comments anyway, with possible date-stamps, changed-by information etc. Otherwise, I think you are stuck with the one-time function-build-up method.
-
Yes David Wasy,
U r right...Performance is the key issue we are facing. More peoples suggested me to index the table on relevant columns from where and how the data is pulled. I 'm seeing the query returning the result quick each time and I 'm really happy about it.
Bingo Ronald San Juan,
Currently i have used the same method to migrate my data you have described. Thanx for your view as well into this thread.
Regards,
Hemant
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply