Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on Jan 10th, 2013  |  syntax: None  |  size: 0.51 KB  |  views: 13  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. declare @s varchar(100) = '1 2 5 22 4 6 -1 -5 -7 6 18 16 -9 2 5 -8 6 9 3 -7';
  2. declare @t int = 15;
  3.  
  4. with i as
  5. (
  6.    select cast(left(@s,charindex(' ',@s+' ')-1) as int) x,
  7.           stuff(@s,1,charindex(' ',@s+' '),'') as s
  8.    union all
  9.    select cast(left(s,charindex(' ',s+' ')-1) as int) x,
  10.           stuff(s,1,charindex(' ',s+' '),'') as s
  11.    from i
  12.    where s <> ''
  13. )
  14. ,z as
  15. (
  16.    select x,rank() over(order by x) r
  17.    from i
  18. )
  19. select distinct a.x, b.x
  20. from z a, z b
  21. where a.x+b.x = @t
  22.   and a.r<b.r
clone this paste RAW Paste Data