Moving column data into one row

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 2008 Forums

Transact-SQL (2008)

Moving column data into one row

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-04-09 : 04:23:31

I have some code that produces;

Select * from #ib

ID FileName Image EventID EventExtraDataID
1 1_1.jpg <Binary> 4 1
2 1_2.jpg <Binary> 4 3
3 1_3.jpg <Binary> 4 4

What I would like is all 3 images in one row;

FileName1 Image1 FileName2 Image2 FileName3 Image3
1_1.jpg <Binary> 1_2.jpg <Binary> 1_3.jpg <Binary>

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-09 : 04:28:07

How What if there are 100 images in the table?

Madhivanan

Failing to plan is Planning to fail

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-04-09 : 05:09:52

There could be a lot of images in the database but this query will only ever return 3 as there are 3 images stored per eventid.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-09 : 05:39:50

check out the PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-09 : 07:13:02

Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Asken
Starting Member

38 Posts

Posted - 2010-04-09 : 07:46:51

If it's always three images you do like this:

-- test table
create table #ib (Id int, FileName varchar(100), Image varchar(15), EventId int, EventExtraId int)

-- test data
insert #ib values (1, '1_1.jpg', 'binary data', 4, 1)
insert #ib values (2, '1_2.jpg', 'binary data', 4, 1)
insert #ib values (3, '1_3.jpg', 'binary data', 4, 1)

select
	i1.FileName AS FileName1,
	i1.Image AS Image1,
	i2.FileName AS FileName2,
	i2.Image AS Image2,
	i3.FileName AS FileName3,
	i3.Image AS Image3
from
	#ib i1

	cross apply (
		select
			FileName, Image
		from
			#ib
		where
			id = 2
	) as i2

	cross apply (
		select
			FileName, Image
		from
			#ib
		where
			id = 3
	) as i3
where
	id = 1

Reporting & Analysis Specialist

Asken
Starting Member

38 Posts

Posted - 2010-04-09 : 07:50:03

PS. Use outer apply instead of cross apply if there are less than three. It will give you null values for the missing images.

Reporting & Analysis Specialist

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-04-11 : 21:01:39

Thanks Asken that will do the trick:) I will also look at the Pivot solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources