concatenating two rows having diff 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 diff value

Author

Topic

raj.prabhu001
Starting Member

16 Posts

Posted - 2011-08-29 : 07:23:56

im gettin out put like this

docno invno suppname desription taxamt
----- ----- --------- ---------- -------
22------12-----hp--------box--------350
22------12-----hp---------ply-------350

i want a single record with "desription" column concatnated for same docno as below

docno invno suppname desription taxamt
----- ----- --------- ---------- -------
22------12-----hp--------box,ply--------350

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 07:37:38

something like


SELECT docno,
docno, docdate ,invno, suppname,taxamt ,
STUFF((SELECT ','+ description FROM table where docno=t.docno FOR XML PATH('')),1,1,'') AS description
FROM (SELECT DISTINCT docno, docdate ,invno, suppname,taxamt FROM Table )t

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

raj.prabhu001
Starting Member

16 Posts

Posted - 2011-08-29 : 07:41:30

my query is this one can u suggest me changes for this query:

SELECT distinct T0.[DocNum] DocNo,'group' as grouper,
T0.objtype as 'objtype',
T0.[Series] DocSeries,
T0.[DocEntry] DocEntry,
T0.[DocDate] DocDate,
T0.[NumAtCard] InvNo,
T0.[TaxDate] InvDate,
T2.[CardCode] SuppCode,
T2.[CardName] SuppName,
-- T9.Name 'State',
Isnull(T8.Dscription,'') Description,

-------------------------------CST2--------------------------------------------------

isnull((Select top 1 udt.u_rate1
From PCH4 a
Join OSTT b on b.AbsId = a.Statype
Join [@AGT_DVATTAX_RPT] udt on a.taxRate = udt.U_rate1 and b.Name = udt.name
Where a.DocEntry = T1.DocEntry and udt.Code = 9
),0)as taxrateCST2,
isnull((Select Sum(a.BaseSum)
From PCH4 a
JOIN OSTT b ON b.AbsID=a.StaType
Join [@AGT_DVATTAX_RPT] udt on a.taxRate = udt.U_rate1 and b.Name = udt.name
Where a.docentry = T1.docentry and udt.Code = 9
and a.ExpnsCode < 0
),0) as CST2TaxbleValue ,
(isnull((Select Sum(a.Taxsum)
From PCH4 a
JOIN OSTT b ON b.AbsID=a.StaType
Join [@AGT_DVATTAX_RPT] udt on a.taxRate = udt.U_rate1 and b.Name = udt.name
Where a.docentry = T1.docentry
and a.ExpnsCode < 0
AND udt.Code = 9 ),0)
+ isnull((Select Sum(a.VatSum)
From PCH3 a
JOIN OSTC b ON a.TaxCode = b.Code
Join [@AGT_DVATTAX_RPT] udt on b.Rate = udt.U_rate1
Where a.docentry = T0.docentry and a.ExpnsCode > 0
AND udt.Code = 9 and a.VatSum <> 0 ),0)) as CST2TaxAmt,

---------------------------------CST4------------------------------------------------------

isnull((Select top 1 udt.u_rate1
From PCH4 a
Join OSTT b on b.AbsId = a.Statype
Join [@AGT_DVATTAX_RPT] udt on a.taxRate = udt.U_rate1 and b.Name = udt.name
Where a.DocEntry = T1.DocEntry and udt.Code = 10
),0)as taxrateCST4,

isnull((Select Sum(a.BaseSum)
From PCH4 a
JOIN OSTT b ON b.AbsID=a.StaType
Join [@AGT_DVATTAX_RPT] udt on a.taxRate = udt.U_rate1 and b.Name = udt.name
Where a.docentry = T1.docentry and udt.Code = 10
and a.ExpnsCode < 0
),0) as CST4TaxbleValue ,

(isnull((Select Sum(a.Taxsum)
From PCH4 a
JOIN OSTT b ON b.AbsID=a.StaType
Join [@AGT_DVATTAX_RPT] udt on a.taxRate = udt.U_rate1 and b.Name = udt.name
Where a.docentry = T1.docentry
and a.ExpnsCode < 0
AND udt.Code = 10 ),0)
+ isnull((Select Sum(a.VatSum)
From PCH3 a
JOIN OSTC b ON a.TaxCode = b.Code
Join [@AGT_DVATTAX_RPT] udt on b.Rate = udt.U_rate1
Where a.docentry = T0.docentry and a.ExpnsCode > 0
AND udt.Code = 10 and a.VatSum <> 0 ),0)) as CST4TaxAmt

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 07:47:08

put it in temporary table using select ...into #Temp. then use my query replacing table with #Temp

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

raj.prabhu001
Starting Member

16 Posts

Posted - 2011-08-29 : 07:52:59

my query has many columns can u suggest some another method

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 07:59:49

see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources