SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Optimised way required for query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vishal_sql
Posting Yak Master

101 Posts

Posted - 12/05/2013 :  04:09:28  Show Profile  Reply with Quote
i have below data

Id val
--------------
1 val1
1 val2
2 val3
2 val4

i wanted to achieve as below

Id val
------------------
1 val1 val2
2 val3 val4


So i found below query.
But its cost is very high and when applied for real time data its taking time.

Is there any optimised way to achieve it in sql server ?


CREATE TABLE #temptable(Id INT, val nvarchar(16))


INSERT INTO #temptable VALUES (1, 'val1')
INSERT INTO #temptable VALUES(1, 'val2')
INSERT INTO #temptable VALUES(2, 'val3')
INSERT INTO #temptable VALUES(2, 'val4')

SELECT m1.Id,
STUFF(( SELECT ' ' +m2.val
FROM #temptable m2
WHERE m2.Id = m1.Id
ORDER BY val
FOR XML PATH('')),1,1 ,'') AS val
FROM #temptable m1
GROUP BY m1.Id ;

Ifor
Aged Yak Warrior

574 Posts

Posted - 12/05/2013 :  06:41:25  Show Profile  Reply with Quote
If there are always two Vals per Id, you could try a PIVOT

Something like:


SELECT Id, [1] + ' ' + [2] AS Val
FROM
(
	SELECT Id, Val, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Val) AS rn
	FROM #temptable
) AS S
PIVOT
(
	MAX(VAL)
	FOR rn IN ([1], [2])
) AS P;
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/05/2013 :  07:04:20  Show Profile  Reply with Quote
This any use?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000