concatenating two rows having different value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

concatenating two rows having different value

Author

Topic

raj.prabhu001
Starting Member

16 Posts

Posted - 2011-08-30 : 01:58:39

my query is giving value for single docno i.e 22 but it fails when run
for all records
and giving error message as below:

Msg 8152, Level 16, State 13, Line 19
String or binary data would be truncated.
The statement has been terminated.

my query:
create table #final
( DocNo nvarchar(20),
grouper nvarchar(20),
objtype nvarchar(20),
DocDate nvarchar(20),
InvNo nvarchar(20),
InvDate nvarchar(20),
SuppCode nvarchar(20),
SuppName nvarchar(20),
State nvarchar(20),
Description nvarchar(20)
--EligiblePurchase decimal(10,4),
-- Import decimal(10,4),
--CST0AgstFormC decimal(10,4),
--TINNO nvarchar(20)
)

INSERT INTO #final
SELECT T0.[DocNum] DocNo,
'group' as grouper,
T0.objtype as 'objtype',

T0.[DocDate] DocDate,
T0.[NumAtCard] InvNo,
T0.[TaxDate] InvDate,
T2.[CardCode] SuppCode,
T2.[CardName] SuppName,
T9.Name 'State',
Isnull(T8.Dscription,'') Description

FROM opch T0 INNER JOIN pch1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
LEFT JOIN CRD1 T3 ON T2.CardCode = T3.CardCode AND T0.[PayToCode] = T3.[Address] and T3.AdresType ='S'
LEFT JOIN OCST T9 ON T3.State = T9.Code and T3.Country = T9.Country and T3.AdresType='S'
INNER JOIN OITM T4 ON T1.ItemCode = T4.ItemCode
LEFT JOIN OCHP T8 ON T4.ChapterID = T8.AbsEntry
INNER JOIN OSTC T5 ON T1.TaxCode = T5.Code
INNER JOIN NNM1 T6 ON T0.Series = T6.Series
LEFT JOIN pch12 T7 ON T0.DocEntry = T7.DocEntry

WHERE Right(T6.SeriesName,2) = 00
AND T0.[U_PType]='Interstate'--((@Type <>'Local' or T0.[U_PType] = @Type )and (@Type = 'Local' or T0.[U_PType] in (@Type,'Import')))
AND T0.[DocDate] Between '2011-04-01' and '2011-04-30'
AND (T7.[TransCat] is null or T7.[TransCat]='')
--and t0.docnum = 22

-- select * from @final

-- --------------------------------------------------------------------------------------------------------------

SELECT DISTINCT s1.docno,

objtype,
DocDate ,
InvNo,
InvDate,
SuppCode,
SuppName,
State,
STUFF((SELECT DISTINCT TOP 100 PERCENT ' , ' + s2.Description
FROM #final AS s2
WHERE s2.docno = s1.docno ORDER BY ' , ' + s2.Description FOR XML PATH('')), 1, 1, '') AS Description

FROM #final AS s1
ORDER BY s1.docno

SELECT DISTINCT s1.docno,

objtype,
DocDate ,
InvNo,
InvDate,
SuppCode,
SuppName,
State,
STUFF((SELECT DISTINCT TOP 100 PERCENT ' , ' + s2.Description
FROM #final AS s2
WHERE s2.docno = s1.docno ORDER BY ' , ' + s2.Description FOR XML PATH('')), 1, 1, '') AS Description

FROM #final AS s1
ORDER BY s1.docno

drop table #final

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-08-30 : 06:47:19

One of the values you are trying to put into your table is too large for the field. Try doing a MAX(LEN(column_Name)) on each column and see if you can find the culprit. I would guess it is the DESCRIPTION column.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 09:33:12

Description seems to be a good candidate for this error. check if you've Description values over 20 characters long

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gwilson67
Starting Member

42 Posts

Posted - 2011-08-30 : 09:49:05

Just make your char fields larger or do a length function on the column to find out which field is too small.

Greg
http://www.freewebstore.org/tsqlcoderepository
Powerful tool for SQL Server development

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources