• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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
0
garethtnash
Asked:
garethtnash
  • 10
  • 8
1 Solution
 
garethtnashAuthor Commented:
That looks extremely confusing, how about just adding a column with the values 1, 2, 3, 4 & 5?

Possible?
0
 
garethtnashAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
garethtnashAuthor Commented:
Actually that doesn't solve my issue :(
0
 
dwe761Software EngineerCommented:
Take a look here while I try to write something up for your situation.

http://msdn.microsoft.com/en-us/library/ms177410.aspx
0
 
garethtnashAuthor Commented:
Thanks I'm there but struggling to understand -
0
 
garethtnashAuthor Commented:
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
 
dwe761Software EngineerCommented:
Close but you're missing something between "for   in"
0
 
garethtnashAuthor Commented:
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
 
dwe761Software EngineerCommented:
What would be your first column name followed by the 5 columns of images?
0
 
dwe761Software EngineerCommented:
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
 
garethtnashAuthor Commented:
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
 
dwe761Software EngineerCommented:
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
 
garethtnashAuthor Commented:
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
 
hyphenpipeCommented:
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
 
dwe761Software EngineerCommented:
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
 
garethtnashAuthor Commented:
Thank you, gratefful if you could give me a 'dummies' explanation - thank you so much
0
 
dwe761Software EngineerCommented:
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
 
garethtnashAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now