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;
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-05 : 07:04:20
This any use?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
Go to Top of Page
   

- Advertisement -