We help IT Professionals succeed at work.

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

woodwyn
woodwyn asked
on
Medium Priority
300 Views
Last Modified: 2012-05-12
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
Comment
Watch Question

Top Expert 2010

Commented:
This should work for you:

DECLARE @cString AS varchar(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + CompanyName FROM Company
SELECT @listStr
Top Expert 2010
Commented:
corrected the variable name

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

This will store all the result into a single variable @cString irrespective of number of columns
Commented:
try like this

DECLARE @cString AS varchar(8000)
Select @cString = COALESCE( @cString + ',' , '') + isnull(CompanyName,'') from Company
Select @cString
Try this


create proc GetCompanyNames
as
DECLARE @cString AS varchar(MAX)
SELECT @cString = COALESCE(@cString +',' ,'') + CompanyName FROM Company 
SELECT @cString CompanyNames

exec GetCompanyNames

Open in new window

Author

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

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

Author

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

Author

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

Author

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.