December 7, 2011 at 8:42 am
Hi All,
create table temp (a int, b int, c int, d int, e int, f int)
insert temp values (21, 45, 11, 16, 567, 1256)
I want to return '21, 45, 11, 16, 567, 1256' as ouput
i,e a single comma delimited column.
Any help is really appreciated.
Thanks in advance
December 7, 2011 at 8:46 am
this should do the trick
create table #temp (a int, b int, c int, d int, e int, f int)
insert #temp values (21, 45, 11, 16, 567, 1256)
select
convert(varchar,a)+
','+
convert(varchar,b)+
','+
convert(varchar,c)+
','+
convert(varchar,d)+
','+
convert(varchar,e)+
','+
convert(varchar,f)
from
#temp
December 7, 2011 at 10:00 am
I prefer using FOR XML PATH(''), because it
1) automatically handles the conversion
2) automatically handles NULL values
If you're working with character data, you do have to be careful with certain restricted characters that are meaningful in XML, e.g., the ampersand (&).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 7, 2011 at 10:07 am
drew.allen (12/7/2011)
I prefer using FOR XML PATH(''), because it1) automatically handles the conversion
2) automatically handles NULL values
If you're working with character data, you do have to be careful with certain restricted characters that are meaningful in XML, e.g., the ampersand (&).
Drew
And the solution is??? Those are columns, not rows.
I'll let you think about it for a couple minutes ;-).
December 7, 2011 at 11:05 am
Ninja's_RGR'us (12/7/2011)
And the solution is??? Those are columns, not rows.
I'll let you think about it for a couple minutes ;-).
FOR XML PATH('') needs to be handle any data that is passed to it. Just because we often pass it a single column with n rows doesn't mean that it can't handle a single row with m columns or even m columns with n rows.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 7, 2011 at 11:15 am
drew.allen (12/7/2011)
Ninja's_RGR'us (12/7/2011)
And the solution is??? Those are columns, not rows.
I'll let you think about it for a couple minutes ;-).
FOR XML PATH('') needs to be handle any data that is passed to it. Just because we often pass it a single column with n rows doesn't mean that it can't handle a single row with m columns or even m columns with n rows.
Drew
Cool, and the tested solution is? 😉
December 7, 2011 at 11:41 am
Ninja's_RGR'us (12/7/2011)
Cool, and the tested solution is? 😉
I thought that the FOR XML PATH('') solution was probably not the best solution in this particular case, so I didn't test it. If nothing else, the original solution provided is much more intuitive than using FOR XML PATH('').
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 8, 2011 at 1:37 am
i'm not all that clued up on XML as most of the XML work is done in the apps, not in the DB, so if you could provide a solution for the problem that would be great, and will also help me learn that little bit more on XML
December 8, 2011 at 4:08 am
Here's a FOR XML PATH version.
SELECT STUFF((SELECT ',' + newRow
FROM (SELECT CONVERT(VARCHAR(4),newRow) AS newRow
FROM (SELECT a,b,c,d,e,f
FROM #temp) t
UNPIVOT (newRow FOR Type IN (a,b,c,d,e,f)) u) a
FOR XML PATH('')), 1, 1, '')
December 8, 2011 at 5:50 am
Cadavre (12/8/2011)
Here's a FOR XML PATH version.
SELECT STUFF((SELECT ',' + newRowFROM (SELECT CONVERT(VARCHAR(4),newRow) AS newRow
FROM (SELECT a,b,c,d,e,f
FROM #temp) t
UNPIVOT (newRow FOR Type IN (a,b,c,d,e,f)) u) a
FOR XML PATH('')), 1, 1, '')
Not devious enough.
December 8, 2011 at 6:29 am
Ninja's_RGR'us (12/8/2011)
Cadavre (12/8/2011)
Here's a FOR XML PATH version.
SELECT STUFF((SELECT ',' + newRowFROM (SELECT CONVERT(VARCHAR(4),newRow) AS newRow
FROM (SELECT a,b,c,d,e,f
FROM #temp) t
UNPIVOT (newRow FOR Type IN (a,b,c,d,e,f)) u) a
FOR XML PATH('')), 1, 1, '')
Not devious enough.
Also, not the same as the other suggested solution since my version will concatenate all into 1 row.
e.g.
BEGIN TRAN
SET NOCOUNT ON
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 (ABS(CHECKSUM(NEWID())) % 100) + 1 AS a,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS b,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS c,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS d,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS e,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS f
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
SELECT COUNT(*) FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== CONVERT =========='
SET STATISTICS TIME ON
SELECT convert(VARCHAR, a) + ',' + convert(VARCHAR, b) + ',' + convert(VARCHAR, c) + ',' + convert(VARCHAR, d) + ',' + convert(VARCHAR, e) + ',' + convert(VARCHAR, f)
FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== UNPIVOT / FOR XML =========='
SET STATISTICS TIME ON
SELECT STUFF((SELECT ',' + newRow
FROM (SELECT CONVERT(VARCHAR(4),newRow) AS newRow
FROM (SELECT a,b,c,d,e,f
FROM #testEnvironment) t
UNPIVOT (newRow FOR Type IN (a,b,c,d,e,f)) u) a
FOR XML PATH('')), 1, 1, '')
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
ROLLBACK
Returns 1,000,000 concatenated strings for the first proposed solution and 1 concatenated string for my solution.
When the test is executed as above, the UNPIVOT method is faster due to the time taken to display the results (1 millions rows takes longer to display than 1 row, who'd have thought? 😀 ).
But if we introduce a holding value: -
BEGIN TRAN
SET NOCOUNT ON
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 (ABS(CHECKSUM(NEWID())) % 100) + 1 AS a,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS b,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS c,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS d,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS e,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS f
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
SELECT COUNT(*) FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
DECLARE @HOLDER VARCHAR(MAX)
PRINT '========== CONVERT =========='
SET STATISTICS TIME ON
SELECT @HOLDER = convert(VARCHAR, a) + ',' + convert(VARCHAR, b) + ',' + convert(VARCHAR, c) + ',' + convert(VARCHAR, d) + ',' + convert(VARCHAR, e) + ',' + convert(VARCHAR, f)
FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== UNPIVOT / FOR XML =========='
SET STATISTICS TIME ON
SELECT @HOLDER = STUFF((SELECT ',' + newRow
FROM (SELECT CONVERT(VARCHAR(4),newRow) AS newRow
FROM (SELECT a,b,c,d,e,f
FROM #testEnvironment) t
UNPIVOT (newRow FOR Type IN (a,b,c,d,e,f)) u) a
FOR XML PATH('')), 1, 1, '')
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
ROLLBACK
========== BASELINE ==========
SQL Server Execution Times:
CPU time = 79 ms, elapsed time = 73 ms.
================================================================================
========== CONVERT ==========
SQL Server Execution Times:
CPU time = 1859 ms, elapsed time = 1867 ms.
================================================================================
========== UNPIVOT / FOR XML ==========
SQL Server Execution Times:
CPU time = 3876 ms, elapsed time = 2863 ms.
================================================================================
December 8, 2011 at 6:52 am
I get very similar results... almost exactly the same figures!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply