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: Wednesday, November 19, 2014 11:42 PM
Points: 11, Visits: 159
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 3,937, Visits: 8,925
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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: Yesterday @ 3:16 PM
Points: 994, Visits: 2,227
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: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
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: Yesterday @ 3:16 PM
Points: 994, Visits: 2,227
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: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
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