Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CONVERT DATA COLUMN TO ROW WTIH COMMA Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 8:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 06, 2013 10:23 AM
Points: 11, Visits: 132
HI ALL,

HOW TO COVERT COLUMN DATA IN TO ROW WITH COMMA SEPARATOR

DATA LIKE THAT :-
TABLE NAME : - TEST
COLUMN NAME : - COL1

ORIGINAL DATA ARE :-

COL1
1
2
3
4
5
6
7
1
4
5

REQUIRED DATA ARE :-

1,2,3,4,5,6,7,1,4,5

HOW TO DO THIS ....

THANKS IN ADVANCE....
Post #1347245
Posted Monday, August 20, 2012 9:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 2,763, Visits: 5,904
Something like this?

DECLARE @Test table(
col1 int)
INSERT INTO @Test
SELECT 1 UNION ALL SELECT
2 UNION ALL SELECT
3 UNION ALL SELECT
4 UNION ALL SELECT
5 UNION ALL SELECT
6 UNION ALL SELECT
7 UNION ALL SELECT
1 UNION ALL SELECT
4 UNION ALL SELECT
5;


WITH CTE AS(
SELECT CAST( (SELECT CAST( col1 AS varchar(10))+ ','
FROM @Test b
FOR XML PATH('')
) AS VARCHAR(MAX)) concat_string
)
SELECT LEFT( concat_string, LEN(concat_string) - 1)
FROM CTE




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1347255
Posted Tuesday, August 21, 2012 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 10:35 AM
Points: 1, Visits: 13
This is what I do.
ex:
col1
1
2
3
4
5
6


declare @text varchar(500)

select @text = isnull(@text + ',','') + col1 from table1

select @text


Hope this help.
Post #1348033
Posted Tuesday, August 21, 2012 10:15 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 985, Visits: 2,198
Using Luis' setup:


DECLARE @Test table(
col1 int)
INSERT INTO @Test
SELECT 1 UNION ALL SELECT
2 UNION ALL SELECT
3 UNION ALL SELECT
4 UNION ALL SELECT
5 UNION ALL SELECT
6 UNION ALL SELECT
7 UNION ALL SELECT
1 UNION ALL SELECT
4 UNION ALL SELECT
5;


DECLARE @S VARCHAR(8000);

SELECT
@S = COALESCE(@S, '') + CAST(col1 AS VARCHAR(10)) + ','
FROM @Test;

SELECT LEFT(@S, LEN(@S)-1); --LEFT is to remove comma at the end


Post #1348172
Posted Tuesday, August 21, 2012 11:50 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:46 AM
Points: 1,371, Visits: 1,547
Slight modification in CTE:

WITH CTE AS(
SELECT CAST( (SELECT CAST( col1 AS varchar(10))+ ','
FROM @Test b
FOR XML PATH('')
) AS VARCHAR(MAX)) concat_string
)
SELECT STUFF(concat_string,1,1,'')
FROM CTE



~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1348191
Posted Wednesday, August 22, 2012 12:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 985, Visits: 2,198
Lokesh Vij (8/21/2012)
Slight modification in CTE:

WITH CTE AS(
SELECT CAST( (SELECT CAST( col1 AS varchar(10))+ ','
FROM @Test b
FOR XML PATH('')
) AS VARCHAR(MAX)) concat_string
)
SELECT STUFF(concat_string,1,1,'')
FROM CTE



Result:
,2,3,4,5,6,7,1,4,5,
Post #1348196
Posted Wednesday, August 22, 2012 2:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:46 AM
Points: 1,371, Visits: 1,547
rVadim (8/22/2012)
Lokesh Vij (8/21/2012)
Slight modification in CTE:

WITH CTE AS(
SELECT CAST( (SELECT CAST( col1 AS varchar(10))+ ','
FROM @Test b
FOR XML PATH('')
) AS VARCHAR(MAX)) concat_string
)
SELECT STUFF(concat_string,1,1,'')
FROM CTE



Result:
,2,3,4,5,6,7,1,4,5,


Here is the corrected version:

WITH CTE AS(
SELECT CAST( (SELECT ',' + CAST( col1 AS varchar(10)) FROM @Test b
FOR XML PATH('')
) AS VARCHAR(MAX)) concat_string
)
SELECT STUFF(concat_string,1,1,'')
FROM CTE






~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1348241
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse