Solved

SQL select as Image1

Posted on 2011-09-08
19
365 Views
Last Modified: 2012-05-12
I have a query which can return anything between 0 and 5 results -

Select
I.Image AS Image
from dbo.[Direct-Product-Image] I
where I.ProductID = 39 AND I.[Default-Image] = 'N'

Open in new window


But really what I want is for the query to do the following -

Return First Record as Image1, Second Record as Image2, Third as Image3 etc
in one row, so instead of have a recordset with 5 records, i have a recordset with 1 record and 5 columns...

Is this possible???

Thank you
0
Comment
Question by:garethtnash
  • 10
  • 8
19 Comments
 
LVL 10

Expert Comment

by:dwe761
ID: 36503252
0
 

Author Comment

by:garethtnash
ID: 36503316
That looks extremely confusing, how about just adding a column with the values 1, 2, 3, 4 & 5?

Possible?
0
 

Author Comment

by:garethtnash
ID: 36503332
I've got it, do you mind sense checking please -

Select
ROW_NUMBER() OVER(ORDER BY I.ID ASC) AS 'ImageNumber',
I.ID,
I.Image AS Image
from dbo.[Direct-Product-Image] I
where I.ProductID = 39 AND I.[Default-Image] = 'N'
0
 

Author Comment

by:garethtnash
ID: 36503376
Actually that doesn't solve my issue :(
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36503430
Take a look here while I try to write something up for your situation.

http://msdn.microsoft.com/en-us/library/ms177410.aspx
0
 

Author Comment

by:garethtnash
ID: 36503470
Thanks I'm there but struggling to understand -
0
 

Author Comment

by:garethtnash
ID: 36503512
I might be completely off track, and i know it doesn't work but -

Select
ROW_NUMBER() OVER(ORDER BY I.ID ASC) AS ImageNumber,
I.ID,
I.Image AS [Image]
from dbo.[Direct-Product-Image] I
pivot [Image] for  in ImageNumber ([1],[2],[3],[4],[5]) as ImageNumber
where I.ProductID = 39 AND I.[Default-Image] = 'N'

Open in new window


Am i close?
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36503543
Close but you're missing something between "for   in"
0
 

Author Comment

by:garethtnash
ID: 36503562
Umm --

Select
ROW_NUMBER() OVER(ORDER BY I.ID ASC) AS ImageNumber,
I.ID,
I.Image AS [Image]
from dbo.[Direct-Product-Image] I
pivot [Image] for ImageNumber in ([1],[2],[3],[4],[5]) as ImageNumber
where I.ProductID = 39 AND I.[Default-Image] = 'N'

Open in new window


Gives me

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Image'.

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 10

Expert Comment

by:dwe761
ID: 36504300
What would be your first column name followed by the 5 columns of images?
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36504340
Try this but replace "MyID" with whatever you want for your first column (the one that has up to 5 images related to it)


-- Try this:
Select MyID, [1],[2],[3],[4],[5]
FROM
	(
	SELECT MyID,
		ROW_NUMBER() OVER(PARTITION BY [MyID] ORDER BY ID ASC) AS ImageNumber,
		Image AS [Image]
	from dbo.[Direct-Product-Image] 
	Where ProductID = 39 AND [Default-Image] = 'N'
	) tab
PIVOT 
	(
	MIN([image])
		 for ImageNumber in ([1],[2],[3],[4],[5]) 
	) p

Open in new window

0
 

Author Comment

by:garethtnash
ID: 36504474
Hi Sorry waas traveling, would that be I.ID?

My table DDL is -

CREATE TABLE [dbo].[Direct-Product-Image](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProductID] [int] NOT NULL,
	[Image] [nvarchar](150) NOT NULL,
	[Default-Image] [char](1) NOT NULL
) ON [PRIMARY]

GO

Open in new window


i tried this I may have put the wrong ID though -

Select ID, [1],[2],[3],[4],[5]
FROM
	(
	SELECT ID,
		ROW_NUMBER() OVER(PARTITION BY [ID] ORDER BY ID ASC) AS ImageNumber,
		Image AS [Image]
	from dbo.[Direct-Product-Image] 
	Where ProductID = 39 AND [Default-Image] = 'N'
	) tab
PIVOT 
	(
	MIN([image])
		 for ImageNumber in ([1],[2],[3],[4],[5]) 
	) p

Open in new window


As this gave me  -

ID      1      2      3      4      5
83      testupload1_12.jpg      NULL      NULL      NULL      NULL
84      test upload 1 _13.jpg      NULL      NULL      NULL      NULL
85      test upload1_32.jpg      NULL      NULL      NULL      NULL

grateful for your help so far - thanks

thank you
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36504574
I'm guessing ID is the table's unique key?  So you wouldn't want that.  That is why you are only getting one image per row.
I would guess it would ProductID.  The reason I didn't think that was it was you have "ProductID = 39" in the WHERE clause.  So therefore your result would only have one row with ProductID 39's five images.

If you remove that part of your WHERE clause, you'll get all 5 images for each ProductID.


Select ProductID, [1],[2],[3],[4],[5]
FROM
	(
	SELECT ProductID,
		ROW_NUMBER() OVER(PARTITION BY [ProductID] ORDER BY ID ASC) AS ImageNumber,
		Image AS [Image]
	from dbo.[Direct-Product-Image] 
	Where [Default-Image] = 'N'
	) tab
PIVOT 
	(
	MIN([image])
		 for ImageNumber in ([1],[2],[3],[4],[5]) 
	) p

Open in new window

0
 

Author Comment

by:garethtnash
ID: 36504655
Thank you  that works perfectly, can we use different column names though, Like Image1, Image2 etc?

I tried -

Select ProductID, [Image1],[Image2],[Image3],[Image4],[Image5]
FROM
	(
	SELECT ProductID,
		ROW_NUMBER() OVER(PARTITION BY [ProductID] ORDER BY ID ASC) AS ImageNumber,
		Image AS [Image]
	from dbo.[Direct-Product-Image] 
	Where [Default-Image] = 'N' AND ProductID = 39
	) tab
PIVOT 
	(
	MIN([image])
		 for ImageNumber in ([Image1],[Image2],[Image3],[Image4],[Image5]) 
	) p
GO

Open in new window

but got

Msg 8114, Level 16, State 1, Line 14
Error converting data type nvarchar to bigint.
Msg 473, Level 16, State 1, Line 14
The incorrect value "Image1" is supplied in the PIVOT operator.


Thank you so much :)
0
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 36504999
Don't want points.  This should work.
Select ProductID, [Image1],[Image2],[Image3],[Image4],[Image5]
FROM
	(
	SELECT ProductID,
		'Image' + convert(varchar, ROW_NUMBER() OVER(PARTITION BY [ProductID] ORDER BY ID ASC)) AS ImageNumber,
		Image AS [Image]
	from dbo.[Direct-Product-Image] 
	Where [Default-Image] = 'N' AND ProductID = 39
	) tab
PIVOT 
	(
	MIN([image])
		 for ImageNumber in ([Image1],[Image2],[Image3],[Image4],[Image5]) 
	) p
GO

Open in new window

0
 
LVL 10

Accepted Solution

by:
dwe761 earned 500 total points
ID: 36505006
The reason that doesn't work is because you are generating those 1-5 numbers by doing the ROW_NUMBER function.

In order to change that, you'd have to give those fields aliases after those column names ([1],[2],...) get generated.

Keep in mind though that this solution only works when the number of columns are known


Select VendorID, [1] AS image1 ,[2] AS image2 ,[3] AS image3 ,[4] AS image4 ,[5] As image5
FROM
	(
	SELECT ProductID,
		ROW_NUMBER() OVER(PARTITION BY [ProductID] ORDER BY ID ASC) AS ImageNumber,
		Image AS [Image]
	from dbo.[Direct-Product-Image] 
	Where [Default-Image] = 'N'
	) tab
PIVOT 
	(
	MIN([image])
		 for ImageNumber in ([1],[2],[3],[4],[5]) 
	) p

Open in new window

0
 

Author Closing Comment

by:garethtnash
ID: 36505086
Thank you, gratefful if you could give me a 'dummies' explanation - thank you so much
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36507449
I don't know if there is a "dummies explanation" as you say because PIVOT is a confusing beast at first.  Yours was also a unique situation because there was no aggregate going on (which is typical of many pivots).  And there was no columns so we had to create them (i.e. [1], [2], ...[5])

Rather than try to rewrite what has already been written, let me just point you to some helpful links.  It's by no means a comprehensive list but some of my favorites:

Pivot for Dummies
http://stackoverflow.com/questions/5443505/please-explain-the-parts-of-a-pivot

Crosstab queries using PIVOT in SQL Server 2005
http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server-2005/

Dynamic Pivot Procedure for SQL Server
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

Pivots with Dynamic Columns in SQL Server 2005/2008
http://www.sqlprof.com/blogs/sqldev/archive/2008/04/12/pivots-with-dynamic-columns-in-sql-server-2005-2008.aspx

Microsoft
http://msdn.microsoft.com/en-us/library/ms177410.aspx

Hope this helps.
0
 

Author Comment

by:garethtnash
ID: 36509651
Thank you  dwe761,

I have got another quick question, I'll raise it in another post, But I also now need to do an update on the table, is that possible using the Pivot script above?

What I've got so far is --


CREATE PROCEDURE [dbo].[AdminUpdateProductImages]
(
@ProductID int,
@Image2 nvarchar(50),
@Image3 nvarchar(50),
@Image4 nvarchar(50),
@Image5 nvarchar(50),
@Image6 nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
Select ProductID, [1] AS image1 ,[2] AS image2 ,[3] AS image3 ,[4] AS image4 ,[5] As image5
FROM
	(
	SELECT ProductID,
		ROW_NUMBER() OVER(PARTITION BY [ProductID] ORDER BY ID ASC) AS ImageNumber,
		Image AS [Image]
	from dbo.[Direct-Product-Image] 
	Where [Default-Image] = 'N' AND ProductID = 39
	) tab
PIVOT 
	(
	MIN([image])
		 for ImageNumber in ([1],[2],[3],[4],[5]) 
	) p
	
DECLARE @OldImage1 nvarchar(50)
SELECT @OldImage1 = image1

DECLARE @OldImage2 nvarchar(50)
SELECT @OldImage2 = image2

DECLARE @OldImage3 nvarchar(50)
SELECT @OldImage3 = image3

DECLARE @OldImage4 nvarchar(50)
SELECT @OldImage4 = image4

DECLARE @OldImage5 nvarchar(50)
SELECT @OldImage5 = image5

IF @OldImage1 <> @Image2
Update dbo.[Direct-Product-Image] Set Image = @Image2
where Image = @OldImage1 And ProductID = @ProductID

IF @OldImage2 <> @Image3
Update dbo.[Direct-Product-Image] Set Image = @Image3
where Image = @OldImage2 And ProductID = @ProductID

IF @OldImage3 <> @Image4
Update dbo.[Direct-Product-Image] Set Image = @Image4
where Image = @OldImage3 And ProductID = @ProductID

IF @OldImage4 <> @Image5
Update dbo.[Direct-Product-Image] Set Image = @Image5
where Image = @OldImage4 And ProductID = @ProductID

IF @OldImage5 <> @Image6
Update dbo.[Direct-Product-Image] Set Image = @Image6
where Image = @OldImage5 And ProductID = @ProductID
END

GO

Open in new window


my new post is --

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27299600.html

Thanks again -
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Temporarily disable SQL Replication 7 21
Date conversion in sql server 2012 6 26
replication - alerts? 4 20
Sql query 34 19
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now