Varchar Aggregate?
-
Hello,
if I have a table like
Name varchar(10), Status Varchar(10)
with entries
NameOne StatusOne
NameOne StatusTwo
NameOne StatusThree
NameTwo StatusOne
NameTwo StatusTwo
Is there an easy way to script some SQL that would return the following:
NameOne StatusOne, StatusTwo, StatusThree
NameTwo StatusOne, StatusTwo
i.e. return each name then a list of associated statuses that the name has?
Many thanks...
-
I recently responded to a similar need and this worked for them. See if this concept applies for you... Transforming table rows to columns
Basically you're looking to do a Pivot function with two data columns instead of three.
hope this helps
-
-
Keith,
Yes, there's an easy way and it does NOT require the overhead of a cursor...
Substitute the name of yourtable for "yourtable" everywhere in the code below...
First, make a function that looks like this...
CREATE FUNCTION dbo.ConcatStatus (@Name VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @MyReturn VARCHAR(8000)
SELECT @MyReturn = ISNULL(@MyReturn+', ','')+Status
FROM yourtable
WHERE Name = @Name
ORDER BY Status
RETURN @MyReturn
END
Then, simply do a SELECT like this...
SELECT Name,
dbo.ConcatStatus(Name) AS StatusList
FROM yourtable
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff,
much appreciated - I gave this a go and it does exactly what I'm looking for.
Thanks!
-
Jeff,
I am missing something. This returns multiple lines of the same data...
CREATE TABLE dbo.Pivot( [Name] varchar(15), Status varchar(15))
INSERT INTO Pivot SELECT 'NameOne', 'StatusOne'
INSERT INTO Pivot SELECT 'NameOne', 'StatusTwo'
INSERT INTO Pivot SELECT 'NameOne', 'StatusThree'
INSERT INTO Pivot SELECT 'NameTwo', 'StatusOne'
INSERT INTO Pivot SELECT 'NameTwo', 'StatusTwo'
GO
CREATE FUNCTION dbo.ConcatStatus (@Name varchar(15))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @MyReturn varchar(8000)
SELECT @MyReturn = ISNULL( @MyReturn + ', ', '') + Status
FROM Pivot
WHERE Name = @Name
ORDER BY Status
RETURN @MyReturn
END
GO
SELECT Name, dbo.ConcatStatus(Name) AS StatusList
FROM Pivot
RESULTS:
-----------------------------------------------------------------------------------------
Name StatusList
--------------- ------------------------------------------------------------------
NameOne StatusOne, StatusThree, StatusTwo
NameOne StatusOne, StatusThree, StatusTwo
NameOne StatusOne, StatusThree, StatusTwo
NameTwo StatusOne, StatusTwo
NameTwo StatusOne, StatusTwo
I wasn't born stupid - I had to study.
-
Keith and Farrell,
Thank you both for your feedback... My bad... the usage should be like this...
SELECT DISTINCT Name,
dbo.ConcatStatus(Name) AS StatusList
FROM yourtable
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply