0

i have the following table: 

RECID OBJECTIF  
1001  1001  
1001  1002  
1001  1003  
2001  2001  
2001  2002

and i need a result like this: 

RECID MyFIELD  
1001  1001-1002-1003  
2001  2001-2002

just if you can give me a hint on what to use, no need to write the code

please take into consideration that i have millions of records in my table

thanks

Taryn
  • 242,637
  • 56
  • 362
  • 405
Abd H.
  • 3
  • 2
  • You already found the answer:pivot. You can try some code and ask about it if you get stuck. – Kuzgun Oct 02 '13 at 10:04
  • I am using SQL Server. So will the "pivot" be the ideal solution over millions of records in 1 table? – Abd H. Oct 02 '13 at 12:52

1 Answers1

2

Here you go....I am not sure about the performance with millions of records...But still I feel this is better than going for a cursor based approach....

SELECT * FROM [PivotSample]

RECID   OBJECTIFSEQUENCE
1001    1002    1
1001    1003    3
1001    1001    2
2001    2001    3
2001    2002    4
2001    2003    2
2001    2004    1

Answer query is given below..

SELECT [RECID]
      ,STUFF((SELECT '- ' + CAST([OBJECTIF] AS varchar), REPLACE(MAX(SEQUENCE),MAX(SEQUENCE),'') FROM [PivotSample] B  WHERE B.[RECID]=A.[RECID] 
        GROUP BY [OBJECTIF],SEQUENCE ORDER BY SEQUENCE  FOR XML PATH('')), 1, 1, '' ) AS OBJECTIVE 
  FROM [PivotSample] A
GROUP BY [RECID]

RECID   OBJECTIVE
1001     1002- 1001- 1003
2001     2004- 2003- 2001- 2002
S Thomas
  • 42
  • 3
  • your answer id pretty good .. +1 to encourage you .. but please do use code formatting **({})** part while you answer .. for more information on code block formatting pls see this .. http://meta.stackexchange.com/a/22189/232122 – Dhaval Oct 02 '13 at 11:38
  • Thanks Dhaval for formatting the results.. i am new to Stackoverflow.. :) – S Thomas Oct 02 '13 at 11:40
  • that is why i put a link for your information you can have a look at the link if you wish .. and again thanks for your answer – Dhaval Oct 02 '13 at 11:41
  • thank you, very nice. i would like to know, will this code give the best performance when applied on millions of records in a table? and i will not be able to create any other table/query on the database, i replace the [PivotSample] by the select statement, but it didnt work, as i am having an order by inside, is there any other option to consider? – Abd H. Oct 02 '13 at 12:20
  • Could you please share your original query here? – S Thomas Oct 02 '13 at 13:26
  • select recid, objectid, sequence, path from mytable order by recid, sequence – Abd H. Oct 02 '13 at 14:14
  • Hi I need the full query with my changes which you told me that not working. You told me that you just replaced Tablename. I need that full query. – S Thomas Oct 02 '13 at 14:29
  • select RECID, RTRIM((SELECT CAST(OBJECTID AS VARCHAR) + ' ' FROM (select RECID, OBJECTID, SEQUENCE, [PATH] FROM tifftable order by recid,sequence) B WHERE B.RECID=A.RECID GROUP BY OBJECTID FOR XML PATH(''))) AS OBJECTID FROM (select RECID, OBJECTID, SEQUENCE, [PATH] FROM tifftable order by recid,sequence) A GROUP BY RECID – Abd H. Oct 03 '13 at 05:39
  • if you remove the order by it should work, but i need it – Abd H. Oct 03 '13 at 05:51
  • Could you plese try the query and get back? – S Thomas Oct 03 '13 at 11:09
  • That is great thank you. I also replaced the [PivotSample] with the select statement, and it worked as it should... just one thing: what about performance? is it the best for millions of records? – Abd H. Oct 04 '13 at 05:50