Separating values of a column with a comma
-
I have a table like this:
column1 column2 column3
1 100 11111
2 101 22222
2 102 33333
Would like to display like this:
column2 column3
1 100
2 22222, 33333
Any suggestions.
thanks,
fz
-
In this solution I used a function (available in MSSQL 8.0 and later). The usage of a function give the advantage to be invoked inside any query, thus executing the concatenation inline, without the need to prepare its value before the query.
In the following script You will find:
- A test table, with the structure You provided.
- The function that actually generates the concatenation result.
- An Insert statement to fill in the test table.
- A Select sentence to return only the first and the third columns.
- A select sentence to return exactly the result You asked.
- Two Drop statements, just to clean your test db from all these stuff.
Bye,
Daniele.
CREATE TABLE dbo.TMP_DATA
(
column1 int NOT NULL,
column2 int NOT NULL,
column3 int NOT NULL
)
GO
CREATE FUNCTION dbo.FNTMP_DATA
(
@nColumn1 int
)
RETURNS varchar (1000)
AS
BEGIN
DECLARE @cList varchar (1000)
SET @cList = ''
SELECT @cList = @cList
+ CASE @cList
WHEN '' THEN ''
ELSE ', '
END
+ CONVERT (varchar, column3)
FROM dbo.TMP_DATA
WHERE column1 = @nColumn1
RETURN @cList
END
GO
INSERT INTO dbo.TMP_DATA
SELECT 1, 100, 11111
UNION SELECT 2, 101, 22222
UNION SELECT 2, 102, 33333
-- Result 1
SELECT DISTINCT
column1,
dbo.FNTMP_DATA (column1)
FROM dbo.TMP_DATA
-- Result 2
SELECT DISTINCT
T.column1,
CASE C.amount
WHEN 1 THEN CONVERT (varchar, T.column2)
ELSE dbo.FNTMP_DATA (T.column1)
END
FROM dbo.TMP_DATA T,
(
SELECT column1,
COUNT (*) AS amount
FROM dbo.TMP_DATA
GROUP BY column1
) C
WHERE C.column1 = T.column1
DROP FUNCTION dbo.FNTMP_DATA
DROP TABLE dbo.TMP_DATA
-
Thanks a lot for your answer. I made it work with little bit of changes to your code.
fz
-
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply