T SQL Query - SQL Server Forums

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

T SQL Query

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2011-06-24 : 16:12:58

Hi,

I have a table which holds such sample data :

InPayid  LineNum  Data 
1234     20       200
1234     21       435672
1234     50       777200435672 

1235     20       201
1235     21       778909

We only need to pick those InPayid rows which have LineNum 20 and 21. We should not pick those InPayid rows which have LineNum 50 as well. When we pick InPayid which has 20 and 21 we should combine the Data field results .

The query should give results and should ignore InPayid 1234 as it has line num 50:

InPayid    Data
1235       201778909

Please advice ...how can we write such query :

Thank you,

Dp

singularity
Posting Yak Master

153 Posts

Posted - 2011-06-24 : 18:45:40

[code]
select InPayid,
max(case when LineNum = 20 then Data else '' end) + max(case when LineNum = 21 then Data else '' end) as Data
from yourtable a
where not exists (select *
from yourtable b
where a.InPayId = b.InPayId and
b.LineNum = 50)
group by InPayId
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-25 : 14:01:30

i think what you need is this


SELECT InPayid,t.u
FROM
(
SELECT InPayid  
FROM table
GROUP BY InPayid  
HAVING SUM(CASE WHEN LineNum  = 50 THEN 1 ELSE 0 END)=0
)t
CROSS APPLY (SELECT Data + ''
             FROM table
             WHERE InPayid  = t.InPayid  
             AND LineNum IN (20,21)
             FOR XML PATH('')
             )t(u)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources