combine multiple rows, multiple criteria.

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

combine multiple rows, multiple criteria.

Author

Topic

dodyryda1
Starting Member

12 Posts

Posted - 2012-01-06 : 16:52:02

hi..

hoping for some help as a little out of my depth. I need to combine multiple rows in a statement. I've had some success with a similar query using FOR XML Path as shown below.. but this one is troublin me.

With reference to this data sample

What I wish to achieve is to select qty item no and description with a status of 5 then text 'exchanged with' then select rows where exfrom column match the recid column in first part.. all on one line..

ie. 1.00 x [HA4520] Petrol Bunyan, 1.00 x [TBAR] Bunyan T-Bar
exchanged with 1.00 x [HA4535] Petrol Bunyan, 1.00 x [TBAR] Bunyan T-Bar

I just dont know how to get this on a single line.. can anyone help? My brain is drained!

Thanks Nath

SELECT DISTINCT
OnSameLine = substring( ( SELECT ', ' + coalesce (CAST(CNT.qty AS varchar(1000)), '')+ ' x ' + '['+ coalesce (CNT.Itemno,'') + ']' + ' ' + coalesce (STK.Desc#1,'') 
FROM [SERVER].db.dbo.Contitems CNT 
INNER JOIN [SERVER].db.dbo.Contracts TBL on CNT.ContNO = TBL.ContNo
INNER JOIN [SERVER].db.dbo.stock STK on CNT.ItemNo = STK.ItemNo
WHERE (CNT.ContNO = cnt1.Contno) and cnt.status = 5 FOR XML path(''), elements
),2,500) + 'exchanged With' + substring( ( SELECT ', ' + coalesce (CAST(CNT.qty AS varchar(1000)), '')+ ' x ' + '['+ coalesce (CNT.Itemno,'') + ']' + ' ' + coalesce (STK.Desc#1,'') 
FROM [SERVER].db.dbo.Contitems CNT 
INNER JOIN [SERVER].db.dbo.Contracts TBL on CNT.ContNO = TBL.ContNo
INNER JOIN [SERVER].db.dbo.stock STK on CNT.ItemNo = STK.ItemNo
WHERE (CNT.ContNO = cnt1.Contno) and **MATCH RECID IDS** FOR XML path(''), elements
),2,500)
FROM [SERVER].db.dbo.Contitems CNT1
INNER JOIN [SERVER].db.dbo.Contracts TBL1 on CNT1.ContNO = TBL1.ContNo
Inner join [SERVER].db.dbo.Depots DEP on TBL1.Hiredepot = DEP.Code
FULL OUTER JOIN dbo.calendar CAL on (cal.[Job Date] = CNT1.DelDate)
FULL OUTER JOIN dbo.JobSpec JB on (jb.PK = cal.ID)
FULL OUTER JOIN [SERVER].db.dbo.DelCharges DEL on TBL1.ContNo = DEL.ContNo
WHERE TBL1.status <> 9 and cnt1.contno = 'sc00031117'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 00:45:41

sorry i cant view posted screenshot
would you mind posting data sample here?

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

dodyryda1
Starting Member

12 Posts

Posted - 2012-01-07 : 02:41:31

hi sorry not sure what happened there..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 03:12:52

[code]
SELECT ContNo,Acct,Type,t.ItemNo + ' ' + t.ItemDesc + CASE WHEN t1.ItemNo > '' THEN 'Exchanged with ' +t1.ItemNo + ' ' + t1.ItemDesc ELSE '' END
FROM Table t
OUTER APPLY (SELECT ItemNo,ItemDesc
FROM table
WHERE exfrom = t.RecID
)t1
WHERE t.exfrom IS NULL OR t.exfrom =''
[/code]

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

dodyryda1
Starting Member

12 Posts

Posted - 2012-01-07 : 08:42:20

thanks ! I'll give it a go.. and let you know

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 08:46:00

ok...will wait for your feedback

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

dodyryda1
Starting Member

12 Posts

Posted - 2012-01-07 : 11:42:08

Thanks so much..
this looks pretty good now have the correct items but on two lines (as below) how could I say get them on 1 line so I have
Bunyan 4520 and Tbar Handle exchanged with bunyan 4435 and Tbar handle

SELECT ContNo,Acct,Type,t.ItemNo + ' ' + t.ItemDesc + CASE WHEN  t1.ItemNo > '' THEN ' Exchanged with ' +t1.ItemNo + ' ' + t1.ItemDesc ELSE '' END
FROM  [SERVER].db.dbo.Contitems t
OUTER APPLY (SELECT ItemNo,ItemDesc
             FROM [SERVER].db.dbo.Contitems
             WHERE exfrom = t.RecID
             )t1 
WHERE t.status <> 9 and t.contno = 'sc00031117' and t.status = 5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 11:47:21

you mean this?


;With CTE
AS
(
SELECT ContNo,Acct,Type,t.ItemNo + ' ' + t.ItemDesc + CASE WHEN  t1.ItemNo > '' THEN ' Exchanged with ' +t1.ItemNo + ' ' + t1.ItemDesc ELSE '' END AS Description
FROM  [SERVER].db.dbo.Contitems t
OUTER APPLY (SELECT ItemNo,ItemDesc
             FROM [SERVER].db.dbo.Contitems
             WHERE exfrom = t.RecID
             )t1 
WHERE t.status <> 9 and t.contno = 'sc00031117' and t.status = 5
)
SELECT ContNo,Acct,Type,
STUFF((SELECT ',' + Description FROM CTE WHERE ContNo=c.ContNo AND Acct=c.Acct AND Type = c.Type FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT ContNo,Acct,Type FROM CTE) c

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

dodyryda1
Starting Member

12 Posts

Posted - 2012-01-07 : 12:20:29

You are a legend sir! many thanks

would there be any way of making the exchanged with only appear once?

dodyryda1
Starting Member

12 Posts

Posted - 2012-01-07 : 13:47:27

nailed it using your help as below..once again many thanks,.

;With CTE
AS
(
SELECT distinct t2.ContNo,t2.Acct,t2.Type,
substring( ( SELECT ', ' + '['+ coalesce (t.Itemno,'') + ']' + ' ' + coalesce (t.ItemDesc,'')
FROM [SERVER].db.dbo.Contitems t
OUTER APPLY (SELECT ItemNo,ItemDesc
FROM [SERVER].db.dbo.Contitems
WHERE exfrom = t.RecID
)t1
WHERE t.contno = t2.contno and t.status <> 9 and t1.itemno <> '' FOR XML path(''), elements ),2,500)
+ ' Exchanged With'
+ substring( ( SELECT ', ' + '['+ coalesce (t3.Itemno,'') + ']' + ' ' + coalesce (t3.ItemDesc,'')
FROM [SERVER].db.dbo.Contitems t3
OUTER APPLY (SELECT ItemNo,ItemDesc
FROM [SERVER].db.dbo.Contitems
WHERE exto = t3.RecID
)t4
WHERE t3.contno = t2.contno and t3.status <> 9 and t4.itemno <> '' FOR XML path(''), elements ),2,500) As Description
FROM [SERVER].db.dbo.Contitems t2
WHERE t2.contno in (select distinct c1. contno
FROM [SERVER].db.dbo.Contitems C1 where c1.status = 5)
)
SELECT ContNo,Acct,
STUFF((SELECT ',' + Description FROM CTE WHERE ContNo=c.ContNo AND Acct=c.Acct FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT ContNo,Acct,Description FROM CTE) c

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 02:26:48

welcome

glad that i could be of help

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources