Issue with updating

Question

Issue with updating

sqllearner-339367

Hall of Fame

Points: 3004
More actions
September 12, 2007 at 5:07 am

#180828

Hi There,
Im having the problem when i tried to update the value in the column.
My table has following columns,
ID    Degree
C111       BSC
C111       MSC

ID acts as a Primary Key
I need to make this 2 rows as a single row
as
C111 BSC,MSC
Can any one help me out with this
Cheers,
Vidhya

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

SELECT DISTINCT
        t1.ID
      , STUFF(( SELECT DISTINCT TOP 100 PERCENT
                        ',' + t2.Degree
                FROM    degrees AS t2
                WHERE   t2.ID = t1.ID
                ORDER BY ',' + t2.Degree
              FOR
                XML PATH('')
              ), 1, 1, '') AS Blah
FROM    degrees AS t1
ORDER BY t1.ID

Also have a look at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=391111

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

sqllearner-339367 Hall of Fame Points: 3004 More actions · Answer 2

HI Yeh,

I tried creating a function to hold the values from the select statement.

When they are executed separately they are fine but when used to return table they are thowring the error

Msg 8152, Level 16, State 10, Line 1

String or binary data would be truncated.

The statement has been terminated.

CAN any one help and explain why this is failing.

CREATE FUNCTION DUP()

RETURNS

@DUPLICATE TABLE

(

CODE nVARCHAR(20),CIPHER nVARCHAR(20))

AS

BEGIN

INSERT

INTO @DUPLICATE(CODE,CIPHER)

SELECT

DISTINCT CODE

,

LTRIM( STUFF(( SELECT DISTINCT TOP 100 PERCENT ',' + t2.cipher FROM Duplicate_Rank_List AS t2

WHERE t2.Code = t1.code ORDER BY ',' + t2.cipher FOR XML PATH('') ), 1, 1, ''))

FROM

Duplicate_Rank_List

AS t1

ORDER

BY t1.code

RETURN

END

SELECT

* INTO Mytab FROM DUP()

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

You should increase the column lengths in your

"(CODE nVARCHAR(20),CIPHER nVARCHAR(20))"

to more

e.g.:

(CODE nVARCHAR(1000),CIPHER nVARCHAR(1000))

so that it can hold the concatenated results,

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

vidhyasudha (9/12/2007)
Hi There,
Im having the problem when i tried to update the value in the column.
My table has following columns,
ID Degree
C111 BSC
C111 MSC
ID acts as a Primary Key
I need to make this 2 rows as a single row
as
C111 BSC,MSC
Can any one help me out with this
Cheers,
Vidhya

My word, no... if you want to create a report like this, that's one thing... stored CSV data in a database is another and it's the very, very thing to do. The proper thing to do, in this case, is to create the proper primary key. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)