SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql advanced select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cha_man
Starting Member

3 Posts

Posted - 01/11/2013 :  12:22:49  Show Profile  Reply with Quote
Hi All,

I have a table like below and data will be populated from a hand held scanner system

-------------------------------------------
| scanned_id | scanned_order | scanner_name
-------------------------------------------
| 10001 | 1 | symbol1
-------------------------------------------
| 10002 | 2 | symbol1
-------------------------------------------
| A0001 | 3 | symbol1
-------------------------------------------
| A0002 | 4 | symbol1
-------------------------------------------
| 10003 | 5 | symbol1
-------------------------------------------
| A0001 | 6 | symbol1
-------------------------------------------

Assume scanned_id 10001,10002 and 10003 are students and scanned_id A0001 and A0002 are the answers they have given for a question.
The person who scans make sure he scans the student id first and then the answer/s and scans the next student so on..

after uploading the scanner to the database, I have to query the database and get the result below

--------------------------
| scanned_id | Answer_code
--------------------------
| 10001 |
--------------------------
| 10002 | A0001,A0002
--------------------------
| 10003 | A0001
--------------------------

Can it be done writing a stored procedure with cursor? any suggestion or sample code will be really appreciated

Please help me to get the above result

many thanks...

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/11/2013 :  12:36:14  Show Profile  Reply with Quote
under the current posted table structure its not possible as there's nothing which suggests which answers are related to which scanned_id values.

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

Go to Top of Page

bitsmed
Constraint Violating Yak Guru

394 Posts

Posted - 01/11/2013 :  18:11:15  Show Profile  Reply with Quote
I think this might work for you:

select a.scanned_id
      ,stuff((select ','+b.scanned_id
               from thetable as b
              where b.scanned_id like 'A%'
                and b.scanned_order between a.scanned_order+1 and a.next_student_order-1
                for xml path('')
             ),1,1,''
            ) as answer_code
  from (select a.scanned_id
              ,a.scanned_order
              ,min(isnull(b.scanned_order,99999999)) as next_student_order
          from thetable as a
               left outer join thetable as b
                            on b.scanned_order>a.scanned_order
                           and b.scanned_id not like 'A%'
         where a.scanned_id not like 'A%'
       ) as a

The above query has not been syntax checked, so please bear over with me, if this is the case.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/12/2013 :  03:25:56  Show Profile  Reply with Quote
why Answer_code doesnt have any value for 10001?

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

Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 01/12/2013 :  03:43:41  Show Profile  Reply with Quote
warning: untested query

;with normalized_table as 
(
	select t1.scanned_id as student, 
               t2.scanned_id as answer
 	from table_name t1 
		inner join table_name t2
		on t1.scanned_id like '1%'
		and t2.scanned_id  like 'A%'
		and t2.scanned_order > t1.scanned_order
	where not exists 
         	(select * 
	         from table_name
 	         where scanned_order between t1.scanned_order andd t2.scanned_order
	         and scanned_id like '1%')
)
select d.scanned_id, isnull(stuff(c.list,1,1,''),'') as Answer_code
from (select scanned_id 
      from table_name
      where scanned_id like '1%') d
outer apply (select ',' + t.answer
             from normalized_table t
             where t.student = d.scanned_id
             for xml path('')) c(list);
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000