Optimised way required for query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Optimised way required for query

Author

Topic

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2013-12-05 : 04:09:28

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

700 Posts

Posted - 2013-12-05 : 06:41:25

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;

Kristen
Test

22859 Posts

Posted - 2013-12-05 : 07:04:20

This any use?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources