July 21, 2009 at 3:03 pm
I can see there are several examples of this but my example is slightly different and it has made my head sore.
I can't change the code of the application other than the stored procedure otherwise I would have changed the datagrid to repeat the dataset.
I have a table called image (ID, image_name).
I want to create a stored procedure to give a result set of image_name across five columns.
Example: Image
ID Image_name
1 1.jpg
2 2.jpg
3 3.jpg
4 4.jpg
5 5.jpg
6 6.jpg
7 7.jpg
8 8.jpg
9 9.jpg
10 10.jpg
Example: output
1 2 3 4 5
1.jpg 2.jpg 3.jpg 4.jpg 5.jpg
6.jpg 6.jpg 7.jpg 8.jpg 9.jpg
10.jpg
Any help appreciated. My solution is getting more and more complex and still doesn't quite work. A cursor is another idea which I haven't done anything with but I have concerns with performance as this is a main grid in the application.
July 21, 2009 at 6:34 pm
Just a matter of reaching into the bag of tricks:
Set up a test environment - note that the ImageId column allows gaps:
CREATE TABLE MyImage
( ImageIDINTEGER NOT NULL
, ImageName VARCHAR(255) NOT NULL
, CONSTRAINT MyImage_PK PRIMARY KEY ( ImageID )
)
INSERT INTO MyImage
(ImageID , ImageName )
INSERT INTO MyImage
(ImageID , ImageName )
SELECT1, '1.jpg' UNION ALL
SELECT3, '2.jpg' UNION ALL
SELECT5, '3.jpg' UNION ALL
SELECT7, '4.jpg' UNION ALL
SELECT11, '5.jpg' UNION ALL
SELECT13, '6.jpg' UNION ALL
SELECT17, '7.jpg' UNION ALL
SELECT19, '8.jpg' UNION ALL
SELECT23, '9.jpg' UNION ALL
SELECT29, '10.jpg' UNION ALL
SELECT31, '11.jpg'
GO
SQL
SELECTMAX(ImageName1) AS ImageName1
,MAX(ImageName2) AS ImageName2
,MAX(ImageName3) AS ImageName3
,MAX(ImageName4) AS ImageName4
,MAX(ImageName5) AS ImageName5
FROM(SELECT( ImageRank - 1 ) / 5 AS ImageGroup
,CASE WHEN ImageRank % 5 = 1 THEN ImageName ELSE NULL END AS ImageName1
,CASE WHEN ImageRank % 5 = 2 THEN ImageName ELSE NULL END AS ImageName2
,CASE WHEN ImageRank % 5 = 3 THEN ImageName ELSE NULL END AS ImageName3
,CASE WHEN ImageRank % 5 = 4 THEN ImageName ELSE NULL END AS ImageName4
,CASE WHEN ImageRank % 5 = 0 THEN ImageName ELSE NULL END AS ImageName5
FROM(
SELECTRANK() OVER ( ORDER BY ImageId ) as ImageRank
,ImageName
FROMMyImage
) as MyImageRanked
) AS MyImagePivot
GROUP BY ImageGroup
Result is
ImageName1 ImageName2 ImageName3 ImageName4 ImageName5
---------- ---------- ---------- ---------- ----------
1.jpg 2.jpg 3.jpg 4.jpg 5.jpg
6.jpg 7.jpg 8.jpg 9.jpg 10.jpg
11.jpg NULL NULL NULL NULL
To see how the logic works:
"RANK() OVER ( ORDER BY ImageId )" assigns a sequential number without any gaps and assigns a column name of ImageRank
Algorithm on ImageRank to get a partition consisting of up to 5 rows is "( ImageRank - 1 ) / 5" - works because integer division does a truncate not a round.
CASE WHEN ImageRank % ? assigns each image value to one of the 5 desired output columns
MAX(ImageName1) keeps the value and discards the nulls
You can group on a column that is not in the select
SQL = Scarcely Qualifies as a Language
July 22, 2009 at 5:17 am
That's great thanks. Very comprehensive and pleased I asked the question. I will have a go tonight when I get back from work.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply