Link to home
Start Free TrialLog in
Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL select as Image1

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
Avatar of dwe761
dwe761
Flag of United States of America image

Avatar of garethtnash

ASKER

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

Possible?
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'
Actually that doesn't solve my issue :(
Take a look here while I try to write something up for your situation.

http://msdn.microsoft.com/en-us/library/ms177410.aspx
Thanks I'm there but struggling to understand -
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?
Close but you're missing something between "for   in"
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'.

What would be your first column name followed by the 5 columns of images?
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

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

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 :)
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

ASKER CERTIFIED SOLUTION
Avatar of dwe761
dwe761
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you, gratefful if you could give me a 'dummies' explanation - thank you so much
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
https://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.
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 --

https://www.experts-exchange.com/questions/27299600/Complicated-SQL-Update-SP.html

Thanks again -