Solved

SQL select as Image1

Posted on 2011-09-08
19
373 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how the fundamental information of how to create a table.

630 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