Trying to group columns into one row
-
Hi,
Im having problems trying to group a column into a single row. I have a table with the columns Ops_Narr_No, Sequence and Narr_Line. Im using the following query. I'm getting a collation error, but I'm not sure why the query wont work.
DECLARE @MaxRank int
DECLARE @Counter int
DROP TABLE #Output
CREATE TABLE #Output(ID int, CText Varchar(6000) )
INSERT INTO #Output SELECT Op_Narr_No, rtrim(Narr_Line) as test FROM dbo.Ops_Narr Where Sequence=00
SELECT @MaxRank = Max(Sequence) From dbo.Ops_Narr
SET @Counter = 2
WHILE @Counter <= @MaxRank
BEGIN
UPDATE A SET A.CText = RTRIM(A.Ctext) + ' ' + RTRIM(Ctext)FROM #Output A INNER JOIN
dbo.Ops_Narr B ON A.ID = B.Op_Narr_No WHERE B.Sequence = @Counter
SET @Counter = @Counter +1
END
i need help asap, im not very good with sql. thanx
-
I would look at the table definitions of Ops_Narr and ensure that BOTH columns are either CHAR with the SAME COLLATION or INTEGER (SANS Collation).
Looks like you are either having a problem with ID = NO or Sequence = @Counter....
Without seeing the DDL of the table Ops_Narr this is a best guess....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I dont quite understand this error when i run the above query i get the following error.
Server: Msg 457, Level 16, State 1, Line 13
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
could you help?
-
Look at the DDL of the tables. One of the columns you are attempting to join on has a different COLLATION then the others. This is causing the problem. 2 different ways to resolve it
1, Redo the collation of the offending columns to match the others
2. Have your SELECT statement perform a collate (see BOL) so they match
Good Hunting!AJ Ahrens
webmaster@kritter.net
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply