Translate

How to select all data of a column in a single column with comma separated


CREATE TABLE #MyTable
(
    in_row_no int PRIMARY KEY,
    vc_value varchar(50) NOT NULL,
    bt_active bit NOT NULL DEFAULT (1)

)

INSERT INTO #MyTable (in_row_no, vc_value) VALUES (1, 'a')
INSERT INTO #MyTable (in_row_no, vc_value) VALUES (2, 'b')
INSERT INTO #MyTable (in_row_no, vc_value) VALUES (3, 'c')
INSERT INTO #MyTable (in_row_no, vc_value) VALUES (4, 'd')
INSERT INTO #MyTable (in_row_no, vc_value) VALUES (5, 'e')
INSERT INTO #MyTable (in_row_no, vc_value) VALUES (6, 'f')
INSERT INTO #MyTable (in_row_no, vc_value) VALUES (7, 'g')
INSERT INTO #MyTable (in_row_no, vc_value) VALUES (8, 'h')
INSERT INTO #MyTable (in_row_no, vc_value) VALUES (9, 'i')
INSERT INTO #MyTable (in_row_no, vc_value) VALUES (10, 'j')


/* Query to get all data of above table */
SELECT * from #MyTable

/* Query to get all data of column vc_value in a single column with comma separated */
SELECT LEFT(vc_value, LEN(vc_value) -1) AS vc_column_name FROM
(
    SELECT
    (
        SELECT vc_value + ',' FROM dbo.#MyTable FOR XML PATH ('')
    ) AS vc_value
) AS vc_value

No comments:

Post a Comment