April 20, 2012 at 11:11 am
is there a way to select the top 5 into a single variable?
for example I have a table that has a column name product and a column named location. I'm trying to retreive the top 5 locations for a selected product. I don't, however, want 5 rows retreived but rather to get the 5 locations returned in one variable.
thanks
April 20, 2012 at 11:22 am
Yes, but may I ask why first? How are you going to be using this information?
April 20, 2012 at 11:29 am
I'm using crystal reports in a vb.net solution. In order to do so I have to set up an .xsd file detailing the fields being returned from a sql SP. Basically the report lists a product, some info about the product and then a list of locations in our facility where the product can be found. the data is coming from a table that has two columns, product & location, where there can be multiple rows returned for product.
its easy to do in crystal outside of vb.net using a subreport, but in vb.net I have to use the .xsd files to define the data coming into the report hence the issue.
April 20, 2012 at 12:36 pm
Something like this?
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'a')
AND type IN (N'U') )
DROP TABLE a;
GO
CREATE TABLE a (id INT PRIMARY KEY)
INSERT INTO dbo.a
(id)
VALUES (1),
(2),
(3),
(4),
(5);
DECLARE @ids VARCHAR(100) = '';
SELECT @ids = @ids + ',' + CAST(id AS VARCHAR(10))
FROM dbo.a
SET @ids = STUFF(@ids, 1, 1, '')
SELECT @ids AS ids;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 20, 2012 at 1:08 pm
Or, an alternative:
CREATE TABLE ProductLocs (
ProductLodID INT IDENTITY(1,1),
Product VARCHAR(10),
Location INT);
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD1',
1 );
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD1',
2 );
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD1',
3 );
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD1',
4 );
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD1',
5 );
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD1',
6 );
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD2',
1 );
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD2',
2 );
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD2',
3 );
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD2',
4 );
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD2',
5 );
INSERT INTO [ProductLocs] (
[Product],
[Location]
) VALUES (
'PROD2',
6 );
SELECT TOP (5)
Product,
Location
FROM
dbo.ProductLocs
WHERE
Product = 'PROD1'
ORDER BY
Location;
DECLARE @Product VARCHAR(10);
SET @Product = 'PROD1';
SELECT STUFF((SELECT TOP (5) ',' + CAST(Location AS VARCHAR) FROM dbo.ProductLocs WHERE Product = @Product ORDER BY Location FOR XML PATH (''), TYPE).value('.','VARCHAR(MAX)'),1,1,'');
DROP TABLE dbo.ProductLocs;
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply