Sql Query help - rearranging the column

Question

Post reply

Sql Query help - rearranging the column

Smash125

Hall of Fame

Points: 3562
More actions
October 9, 2012 at 6:14 am

#268396

CREATE TABLE #temp
(
ID INT,
FirstName NVARCHAR(100),
)
INSERT INTO #temp
SELECT '1','A'
UNION ALL
SELECT '2','B'
UNION ALL
SELECT '3','C'
Out Put should look like
'abc'
How can i achive this

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

Did you really want it lowercase?

SELECT (SELECT LOWER(FirstName) AS "text()"

FROM #temp

ORDER BY ID

FOR XML PATH(''),TYPE).value('.','NVARCHAR(4000)') AS OutPut;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Phil Parkin SSC Guru Points: 246986 More actions · Answer 2

Another way:

declare @BigString varchar(max) = ''

select @BigString = @BigString + Firstname

from #temp

order by id

select @BigString, lower(@BigString)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Yet another way : -

WITH CTE ([Level],IDs,Name) AS (

SELECT TOP 1 0, ID, CAST(FirstName AS NVARCHAR(4000))

FROM #temp

ORDER BY ID

UNION ALL

SELECT [Level] + 2, ID, CAST(Name+FirstName AS NVARCHAR(4000))

FROM #temp

INNER JOIN CTE ON ID = IDs+1)

SELECT TOP 1 Name, LOWER(Name)

FROM CTE

ORDER BY [Level] DESC

OPTION (MAXRECURSION 0);

I'm not advocating using the above recursive CTE, as it's probably the slowest method of the three on offer.

In fact. . . let's have a quick look 😀

Set-up 4,000 rows of sample data: -

SET NOCOUNT ON;

IF object_id('tempdb..#temp') IS NOT NULL

BEGIN

DROP TABLE #temp;

END;

SELECT TOP 4000 IDENTITY(INT,1,1) AS ID,

CHAR((ABS(CHECKSUM(NEWID())) % 25) + 65) AS FirstName

INTO #temp

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

OK, now let's sort out the three options so that they're on a level playing field: -

WITH CTE ([Level],IDs,Name) AS (

SELECT TOP 1 0, ID, CAST(FirstName AS NVARCHAR(4000))

FROM #temp

ORDER BY ID

UNION ALL

SELECT [Level] + 2, ID, CAST(Name+FirstName AS NVARCHAR(4000))

FROM #temp

INNER JOIN CTE ON ID = IDs+1)

SELECT TOP 1 Name, LOWER(Name)

FROM CTE

ORDER BY [Level] DESC

OPTION (MAXRECURSION 0);

SELECT Name, LOWER(Name)

FROM (SELECT (SELECT FirstName

FROM #temp

ORDER BY ID

FOR XML PATH(''),TYPE

).value('.','NVARCHAR(4000)')

) a(Name);

DECLARE @BigString NVARCHAR(4000) = '';

SELECT @BigString = @BigString + FirstName

FROM #temp

ORDER BY ID;

SELECT @BigString, LOWER(@BigString);

OK, now add some timings: -

DECLARE @start DATETIME2;

PRINT REPLICATE('=',80);

PRINT 'RECURSIVE CTE';

PRINT REPLICATE('=',80);

SET @start = SYSDATETIME();

WITH CTE ([Level],IDs,Name) AS (

SELECT TOP 1 0, ID, CAST(FirstName AS NVARCHAR(4000))

FROM #temp

ORDER BY ID

UNION ALL

SELECT [Level] + 2, ID, CAST(Name+FirstName AS NVARCHAR(4000))

FROM #temp

INNER JOIN CTE ON ID = IDs+1)

SELECT TOP 1 Name, LOWER(Name)

FROM CTE

ORDER BY [Level] DESC

OPTION (MAXRECURSION 0);

PRINT DATEDIFF(ms,@start,SYSDATETIME());

PRINT REPLICATE('=',80);

PRINT '';

PRINT REPLICATE('=',80);

PRINT 'XML PATH';

PRINT REPLICATE('=',80);

SET @start = SYSDATETIME();

SELECT Name, LOWER(Name)

FROM (SELECT (SELECT FirstName

FROM #temp

ORDER BY ID

FOR XML PATH(''),TYPE

).value('.','NVARCHAR(4000)')

) a(Name);

PRINT DATEDIFF(ms,@start,SYSDATETIME());

PRINT REPLICATE('=',80);

PRINT '';

PRINT REPLICATE('=',80);

PRINT 'TRADITIONAL VARIABLE METHOD';

PRINT REPLICATE('=',80);

SET @start = SYSDATETIME();

DECLARE @BigString NVARCHAR(4000) = '';

SELECT @BigString = @BigString + FirstName

FROM #temp

ORDER BY ID;

SELECT @BigString, LOWER(@BigString);

PRINT DATEDIFF(ms,@start,SYSDATETIME());

PRINT REPLICATE('=',80);

Results: -

================================================================================

RECURSIVE CTE

================================================================================

265

================================================================================

XML PATH

================================================================================

16

================================================================================

TRADITIONAL VARIABLE METHOD

================================================================================

15

================================================================================

So the XML and traditional methods are equivalent and the recursive CTE is, as expected, the worst.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

justmohit SSC Veteran Points: 280 More actions · Answer 4

justmohit

SSC Veteran

Points: 280

October 9, 2012 at 11:10 am

#1547303

you can use XML PATH to get the result

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

justmohit (10/9/2012)
you can use XML PATH to get the result

Who'd have thought?

Mark, perhaps?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel