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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Combining Multiple rows in 1 column. posible?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 04/16/2013 :  07:09:47  Show Profile  Reply with Quote
i have a table named tbl_BARCODEgenerated
items
Bcodetxt1, Bcodeimg1, Bcodetxt2 , Bcodeimg2, Bcodetxt3, Bcodeimg3 ,Bcodetxt4, Bcodeimg4
<0000123>, <Byte> , <0000124> , <Byte> , <0000125>, <Byte> ,<0000126>, <Byte>
<0000127>, <Byte> , <0000128> , <Byte> , <0000300>, <Byte> ,<0000253>, <Byte>
that i wanted to look like this

barcode
<0000123>,
<0000124>
<0000125>
<0000126>
<0000127>
<0000128>
<0000300>
<0000253>

and next to that i want to use that barcodes to search for their Description(or other info) in another table it goes like this:

barcode ,Description , Status
<0000123>, kamote kamo, good
<0000124>, karots kamo, good
<0000125>, karote kamo, good
<0000126>, karota kemo, good
<0000127>, kamote kamo, good
<0000128>, kamite oaks, bad
<0000300>, kamote keme, bad
<0000253>, kamote kamo, good

is this posibles??
your suggestions will be appreciated.. !! thanks in advance!!!

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 04/16/2013 :  08:50:39  Show Profile  Reply with Quote
select Bcodetxt1 as Bcodetxt from BARCODEgenerated
union
select Bcodetxt2 from BARCODEgenerated
union
select Bcodetxt3 from BARCODEgenerated
union
select Bcodetxt4 from BARCODEgenerated


and

select barcode ,Description , Status
from anothertable
where barcode in (

select Bcodetxt1 as Bcodetxt from BARCODEgenerated
union
select Bcodetxt2 from BARCODEgenerated
union
select Bcodetxt3 from BARCODEgenerated
union
select Bcodetxt4 from BARCODEgenerated)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/16/2013 :  10:39:03  Show Profile  Reply with Quote
or use UNPIVOT

SELECT m.barcode,n.description,n.status
FROM
(
SELECT barcode
FROM (SELECT Bcodetxt1,  Bcodetxt2 ,  Bcodetxt3, Bcodetxt4
FROM tbl_BARCODEgenerated)t
UNPIVOT(barcode FOR barcodecat IN ([Bcodetxt1],  [Bcodetxt2] ,  [Bcodetxt3], [Bcodetxt4]))u
)m
INNER JOIN Table2 n
ON n.barcode = m.barcode


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 04/16/2013 :  21:26:42  Show Profile  Reply with Quote
Sir Visakh16 can you explain... your code... i can't fully grasp it because we use only sample data...
sorry for this inconvinience
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/17/2013 :  00:38:17  Show Profile  Reply with Quote
quote:
Originally posted by Yonkouturko

Sir Visakh16 can you explain... your code... i can't fully grasp it because we use only sample data...
sorry for this inconvinience


i'm basically applying UNPIVOT which will transpose your data contained in various columns onto single columns by adding them as rows. Then its just a matter of joining to your other table to get related details for the barcode value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.53 seconds. Powered By: Snitz Forums 2000