Combining Multiple rows in 1 column. posible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2012 Forums

Transact-SQL (2012)

Combining Multiple rows in 1 column. posible?

Author

Topic

Yonkouturko
Yak Posting Veteran

59 Posts

Posted - 2013-04-16 : 07:09:47

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

54 Posts

Posted - 2013-04-16 : 08:50:39

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-16 : 10:39:03

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

Yonkouturko
Yak Posting Veteran

59 Posts

Posted - 2013-04-16 : 21:26:42

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-17 : 00:38:17

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources