asked on

Can I combine the results of one column of multiple records into one varaiable in SQL?

I want to create a stored procedure that returns one text string that is the combination of a queries results from the same column. Is there a way to do this in SQL? The production server is still at SQL 2000. I will have no idea how many results I will need to combine with each call.

For Example:
DECLARE @cString AS varchar(8000)
SET @cString=''
SELECT CompanyName INTO #MSR FROM Company
SCAN #MSR
IF @cString = ''
@cString = RTRIM(#MSR.CompanyName)
ELSE
@cString = @cString+', '+RTRIM(#MSR.CompanyName)
ENDSCAN

Om Prakash

This should work for you:

DECLARE @cString AS varchar(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + CompanyName FROM Company
SELECT @listStr

SOLUTION

Om Prakash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

sachinpatil10d

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

woodwyn

ASKER

Finally got to get back to this. Can anyone suggest how to call this code from within a select statement in a stored procedure?

For example,

CREATE PROCEDURE (Test)
PARAMETER1,
PARAMETER2, ...
AS
SELECT
TABLE1.COLUMN1,
TABLE2.COLUMN1,
@cString = ???
FROM ...

This the actual use of your suggestions.
DECLARE @cLocation VARCHAR(2000)
IF object_id('tempdb..#MSR20') IS NOT NULL
BEGIN
DROP TABLE #MSR20
END
DECLARE @cKeyEmpty AS UNIQUEIDENTIFIER
SELECT @cKeyEmpty='00000000-0000-0000-0000-000000000000'
SELECT Number AS Location INTO #MSR20 From ItemsHistory LEFT OUTER JOIN Items ON Items.keyItems = ItemsHistory.keyParent
      WHERE ItemsHistory.keyParent <> @cKeyEmpty AND ItemsHistory.keyItems = '{FBD49A18-8CCF-11D4-88CD-0001024856CF}' AND
            ItemsHistory.Quantity > ItemsHistory.QtyMovedOn
GROUP BY Number
UNION ALL
SELECT Location FROM ItemsHistory LEFT OUTER JOIN Locations ON Locations.keyLocations = ItemsHistory.keyLocations
      WHERE ItemsHistory.keyLocations <> @cKeyEmpty AND ItemsHistory.keyItems = '{FBD49A18-8CCF-11D4-88CD-0001024856CF}' AND
            ItemsHistory.Quantity > ItemsHistory.QtyMovedOn
GROUP BY Location

SELECT @cLocation = COALESCE(@cLocation +',' ,'') + RTRIM(Location) FROM #MSR20
SELECT @cLocation

sachinpatil10d

Try this

Create table tmp(strOut nvarchar(1000))
insert into tmp
exec Test

select * from tmp

Open in new window

or
change the store procedure to function so that you can make a function call as

select * from dbo.test()

Open in new window

woodwyn

ASKER

sach...

I am having a hard time making this last part work. I can't create a function because I need to use a temp table. I created a stored proc with the COALESCE command and this seems to work. Here's the proc.

CREATE PROCEDURE [BuildInventoryEOLLocations]
@cKeyItems UNIQUEIDENTIFIER,
@cLocation VARCHAR(2000) OUTPUT
AS
DECLARE @cKeyEmpty AS UNIQUEIDENTIFIER
SELECT @cKeyEmpty='00000000-0000-0000-0000-000000000000'

SELECT Number AS Location INTO #MSR20 From ItemsHistory LEFT OUTER JOIN Items ON Items.keyItems = ItemsHistory.keyParent
      WHERE ItemsHistory.keyParent <> @cKeyEmpty AND ItemsHistory.keyItems = @cKeyItems AND
            ItemsHistory.Quantity > ItemsHistory.QtyMovedOn
GROUP BY Number
UNION ALL
SELECT Location FROM ItemsHistory LEFT OUTER JOIN Locations ON Locations.keyLocations = ItemsHistory.keyLocations
      WHERE ItemsHistory.keyLocations <> @cKeyEmpty AND ItemsHistory.keyItems = @cKeyItems AND
            ItemsHistory.Quantity > ItemsHistory.QtyMovedOn
GROUP BY Location

SELECT @cLocation = COALESCE(@cLocation +',' ,'') + RTRIM(Location) FROM #MSR20

DROP TABLE #MSR20
GO

woodwyn

ASKER

Here's the proc that I am hoping to capture the results from BuildInventoryEOLLocations within a SELECT statement. This is a lot of code. Just note the Location = 'Temp' lines. It would be great if it could read Location = (EXEC BuildInventoryEOLLocations Items.KeyItems, Location OUTPUT).

CREATE PROCEDURE [BuildInventoryEOL]

      @cKeyCustomers char(36),            -- WebSubDomains.keyCustomers Or WebLogins.keyCustomers
      @cCategoryCodes varchar(8000),      -- Codes will be seperated by a comma
      @cItemNumber char(12),                  -- Search for dbo.Items.Number
      @cDescription varchar(8000),            -- Search all dbo.Items.ItemsDesc, ItemsNotes1 and ItemsNotes2 fields for this string
      @cJobNumber char(10),                  -- Search for items created for this child (and/or parent - Ask Mitch) job number
      @nCreatedForJob TINYINT,            -- If 0 THEN use Items.keyWorkOrders. If 1 THEN ItemsHistory.keyWorkOrders.
      @cWidth char(10),                  -- Users may search by Width, Height and/or Length. These parameters are passed
      @cHeight char(10),                  -- as char. This procdure converts them to decimals.
      @cLength char(10),                  --
      @nStartAtRecord INT,                  -- Starting record number to return
      @nRecordCount INT                  -- How many records to return
AS

-- Add % to front and end of @cDescription
SET @cDescription = '%'+RTRIM(@cDescription)+'%'

DECLARE @cKeyEmpty AS UNIQUEIDENTIFIER
SELECT @cKeyEmpty='00000000-0000-0000-0000-000000000000'

-- Return the result set count as a column
DECLARE @nTotalRecordCount AS INT

/* Measurements are passed a character strings which allows users to not enter anything in a measurement search box and search on 0.
All measurements are 0 or greater, so If an empty string is passed set the measurement to -1 to return all records */
DECLARE @nWidth AS DECIMAL                  -- Users may search by dimensions
DECLARE @nHeight AS DECIMAL                  -- Users may search by dimensions
DECLARE @nLength AS DECIMAL                  -- Users may search by dimensions
IF @cWidth='' SET @cWidth='-1'
IF @cHeight='' SET @cHeight='-1'
IF @cLength='' SET @cLength='-1'
SET @nWidth=CAST(@cWidth AS DECIMAL(10,2))
SET @nHeight=CAST(@cHeight AS DECIMAL(10,2))
SET @nLength=CAST(@cLength AS DECIMAL(10,2))

IF object_id('tempdb..#MSR1') IS NOT NULL
BEGIN
DROP TABLE #MSR1
END
IF object_id('tempdb..#MSR2') IS NOT NULL
BEGIN
DROP TABLE #MSR2
END
IF object_id('tempdb..#MSR3') IS NOT NULL
BEGIN
DROP TABLE #MSR3
END
IF object_id('tempdb..#MSR4') IS NOT NULL
BEGIN
DROP TABLE #MSR4
END
IF object_id('tempdb..#MSR5') IS NOT NULL
BEGIN
DROP TABLE #MSR5
END

/* #MSR5 is used to validate the item is on a job not a T-Job */
SELECT keyWorkOrders INTO #MSR5 FROM Jobs WHERE JobNumber LIKE 'T%' OR ChildJobNum LIKE 'T%'

/*      Now for the 'big' selection                  */
DECLARE @cLocation VARCHAR(2000)
IF @nCreatedForJob = 1
BEGIN
      SELECT
            Items.keyItems, Items.Number, Items.ItemsDesc, Items.dtConstructed, Items.Width, Items.Height, Items.Length,
            Qty = (SELECT SUM(ItemsHistory.Quantity-ItemsHistory.QtyMovedOn) FROM ItemsHistory WHERE
                  ItemsHistory.keyItems = Items.keyItems),

            Location = 'Temp1',
            JobNumber = (CASE Jobs.ChildJobNum
                        WHEN '' THEN Jobs.JobNumber
                        ELSE Jobs.ChildJobNum
                   END),
            Jobs.JobDesc,
            Shows.Size, Shows.ShowDate,
            Category = RTRIM(ItemCategories.Code) + ': '+ItemCategories.Description,
            Origin = (CASE Items.Origin
                        WHEN 0 THEN 'Branch'
                        WHEN 1 THEN 'St. Paul'
                        ELSE 'Other'
                   END),
            LastUsed=(CASE (SELECT COUNT(*)
              FROM ItemsHistory
              WHERE ItemsHistory.keyItems=Items.keyItems
              AND ItemsHistory.keyWorkOrders NOT IN (SELECT keyWorkOrders
              FROM Jobs

              WHERE jobs.keyWorkOrders = ItemsHistory.keyWorkOrders

              AND (LEFT(JobNumber,1) IN ('M','W','T') OR LEFT(ChildJobNum,1) IN ('M','W','T')))
              AND YEAR(ItemsHistory.dtMoved)<>1900)
              WHEN 0 THEN (SELECT TOP 1 dtMoved
              FROM ItemsHistory
              WHERE keyItems=Items.keyItems
              ORDER BY dtMoved DESC)
              ELSE (SELECT TOP 1 dtMoved
              FROM ItemsHistory
              WHERE keyItems=Items.keyItems
              AND keyWorkOrders NOT IN (SELECT keyWorkOrders
              FROM Jobs
       WHERE jobs.keyWorkOrders = ItemsHistory.keyWorkOrders
              AND (LEFT(JobNumber,1) IN ('M','W','T') OR LEFT(ChildJobNum,1) IN ('M','W','T')))
              ORDER BY dtMoved DESC)
              END),
            ImagePath = 'WebGraphics\Picts\'+LEFT(Items.Number,4)+'\'+SUBSTRING(Items.Number,5,1)+'\'
      INTO #MSR1
      FROM Items
            LEFT OUTER JOIN Jobs ON Jobs.keyWorkorders = items.keyWorkorders
            LEFT OUTER JOIN ItemCategories ON ItemCategories.keyItemCategories = Items.keyItemCategories
            LEFT OUTER JOIN WorkOrders ON WorkOrders.keyWorkOrders = Items.keyWorkOrders
            LEFT OUTER JOIN Shows ON Shows.keyShows = WorkOrders.keyShows
      WHERE (keyCustomers = @cKeyCustomers) AND
            (Code IN (SELECT Items FROM dbo.Split(@cCategoryCodes,',')) OR @cCategoryCodes = '') AND
            (Items.Number = @cItemNumber OR @cItemNumber = '') AND
            ((ItemsDesc LIKE @cDescription OR ItemsNotes1 LIKE @cDescription OR ItemsNotes2 LIKE @cDescription) OR @cDescription = '') AND
            (Jobs.ChildJobNum = @cJobNumber OR @cJobNumber = '') AND
            (Items.Width = @nWidth OR @nWidth=-1) AND
            (Items.Height = @nHeight OR @nHeight=-1) AND
            (Items.Length = @nLength OR @nLength=-1) AND
            (@cJobNumber = '' AND Items.isInvalid<>1) AND
            (@cJobNumber = '' AND Items.keyItems IN (SELECT keyItems FROM ItemsHistory WHERE Quantity > QtyMovedOn AND keyWorkOrders NOT IN (SELECT keyWorkOrders FROM #MSR5)))
      ORDER BY Number

      SET @nTotalRecordCount = (SELECT COUNT(*) FROM #MSR1)

      SELECT
      (SELECT COUNT(*) FROM #MSR1 M2 WHERE M2.Number <= M1.Number) AS rownumber, Number, ItemsDesc, dtConstructed, Width, Height, Length,
            ISNULL(Qty,0) AS Qty, ISNULL(Location, 'Unknown') AS Location, ISNULL(JobNumber,'Unknown') AS JobNumber, ISNULL(JobDesc,'') AS JobDesc,
            ISNULL(Size,'') AS Size, ISNULL(ShowDate,'') AS ShowDate, ISNULL(Category,'Unknown') AS Category, Origin,
            ISNULL(LastUsed,'') AS LastUsed, ImagePath, @nTotalRecordCount AS RecCount
      INTO #MSR2
      FROM #MSR1 M1
      ORDER BY Number

      SELECT * FROM #MSR2
      WHERE RowNumber >= @nStartAtRecord AND
       RowNumber < @nStartAtRecord + @nRecordCount
END
ELSE
BEGIN
      DECLARE @cKeyWorkOrders AS UNIQUEIDENTIFIER
      SELECT @cKeyWorkOrders = (SELECT TOP 1 keyWorkOrders FROM Jobs WHERE ChildJobNum = @cJobNumber OR JobNumber = @cJobNumber)
      SELECT
            Items.keyItems, Items.Number, Items.ItemsDesc, Items.dtConstructed, Items.Width, Items.Height, Items.Length,
            Qty = (SELECT SUM(ItemsHistory.Quantity-ItemsHistory.QtyMovedOn) FROM ItemsHistory WHERE
                  ItemsHistory.keyItems = Items.keyItems),

            Location = 'Temp2',
            JobNumber = (CASE Jobs.ChildJobNum
                        WHEN '' THEN Jobs.JobNumber
                        ELSE Jobs.ChildJobNum
                   END),
            Jobs.JobDesc,
            Shows.Size, Shows.ShowDate,
            Category = RTRIM(ItemCategories.Code) + ': '+ItemCategories.Description,
            Origin = (CASE Items.Origin
                        WHEN 0 THEN 'Branch'
                        WHEN 1 THEN 'St. Paul'
                        ELSE 'Other'
                   END),
            LastUsed=(CASE (SELECT COUNT(*)
              FROM ItemsHistory
              WHERE ItemsHistory.keyItems=Items.keyItems
              AND ItemsHistory.keyWorkOrders NOT IN (SELECT keyWorkOrders
              FROM Jobs
              WHERE jobs.keyWorkOrders = ItemsHistory.keyWorkOrders
              AND (LEFT(JobNumber,1) IN ('M','W','T') OR LEFT(ChildJobNum,1) IN ('M','W','T')))
              AND YEAR(ItemsHistory.dtMoved)<>1900)
              WHEN 0 THEN (SELECT TOP 1 dtMoved
              FROM ItemsHistory
              WHERE keyItems=Items.keyItems
              ORDER BY dtMoved DESC)
              ELSE (SELECT TOP 1 dtMoved
              FROM ItemsHistory
              WHERE keyItems=Items.keyItems
              AND keyWorkOrders NOT IN (SELECT keyWorkOrders
              FROM Jobs
       WHERE jobs.keyWorkOrders = ItemsHistory.keyWorkOrders
              AND (LEFT(JobNumber,1) IN ('M','W','T') OR LEFT(ChildJobNum,1) IN ('M','W','T')))
              ORDER BY dtMoved DESC)
              END),
            ImagePath = 'WebGraphics\Picts\'+LEFT(Items.Number,4)+'\'+SUBSTRING(Items.Number,5,1)+'\'
      INTO #MSR3
      FROM Items
            LEFT OUTER JOIN Jobs ON Jobs.keyWorkorders = items.keyWorkorders
            LEFT OUTER JOIN ItemCategories ON ItemCategories.keyItemCategories = Items.keyItemCategories
            LEFT OUTER JOIN WorkOrders ON WorkOrders.keyWorkOrders = Items.keyWorkOrders
            LEFT OUTER JOIN Shows ON Shows.keyShows = WorkOrders.keyShows
      WHERE (Items.keyCustomers = @cKeyCustomers) AND
            (Code IN (SELECT Items FROM dbo.Split(@cCategoryCodes,',')) OR @cCategoryCodes = '') AND
            ((Items.Number = @cItemNumber) OR @cItemNumber = '') AND
            ((ItemsDesc LIKE @cDescription OR ItemsNotes1 LIKE @cDescription OR ItemsNotes2 LIKE @cDescription) OR @cDescription = '') AND
            ((@cJobNumber = '') OR
                  (Items.keyItems IN (SELECT ItemsHistory.keyItems FROM ItemsHistory WHERE keyWorkOrders =@cKeyWorkOrders))) AND
            (Items.Width = @nWidth OR @nWidth=-1) AND
            (Items.Height = @nHeight OR @nHeight=-1) AND
            (Items.Length = @nLength OR @nLength=-1) AND
            (@cJobNumber = '' AND Items.isInvalid<>1) AND
            (@cJobNumber = '' AND Items.keyItems IN (SELECT keyItems FROM ItemsHistory WHERE Quantity > QtyMovedOn AND keyWorkOrders NOT IN (SELECT keyWorkOrders FROM #MSR5)))
      ORDER BY Number

      SET @nTotalRecordCount = (SELECT COUNT(*) FROM #MSR3)

      SELECT
      (SELECT COUNT(*) FROM #MSR3 M4 WHERE M4.Number <= M3.Number) AS rownumber, Number, ItemsDesc, dtConstructed, Width, Height, Length,
            ISNULL(Qty,0) AS Qty, ISNULL(Location, 'Unknown') AS Location, ISNULL(JobNumber,'Unknown') AS JobNumber, ISNULL(JobDesc,'') AS JobDesc,
            ISNULL(Size,'') AS Size, ISNULL(ShowDate,'') AS ShowDate, ISNULL(Category,'Unknown') AS Category, Origin,
            ISNULL(LastUsed,'') AS LastUsed, ImagePath, @nTotalRecordCount AS RecCount
      INTO #MSR4
      FROM #MSR3 M3
      ORDER BY Number

      SELECT * FROM #MSR4
      WHERE RowNumber >= @nStartAtRecord AND
       RowNumber < @nStartAtRecord + @nRecordCount
END

IF object_id('tempdb..#MSR1') IS NOT NULL
BEGIN
DROP TABLE #MSR1
END
IF object_id('tempdb..#MSR2') IS NOT NULL
BEGIN
DROP TABLE #MSR2
END
IF object_id('tempdb..#MSR3') IS NOT NULL
BEGIN
DROP TABLE #MSR3
END
IF object_id('tempdb..#MSR4') IS NOT NULL
BEGIN
DROP TABLE #MSR4
END
IF object_id('tempdb..#MSR5') IS NOT NULL
BEGIN
DROP TABLE #MSR5
END
GO

woodwyn

ASKER

The accepted solutions are perfect for my original question. The followup questions and comments are incomplete and can be ignored.