0

I have a table look like this: 

P_ID    Lang   
1001    EN
1001    German

Expecting result:

P_ID Lang1  Lang2  
1001  EN     German
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Coconuts
  • 25
  • 5
  • same as this question, and it has the answer : http://stackoverflow.com/questions/17076089/convert-row-data-to-column-in-sql-server – dhidy Feb 27 '14 at 09:40

2 Answers2

1

I just had to do a PIVOT like this, so here is my code modified for your situation:

SELECT * FROM
(
    SELECT P_ID,
           Lang,
           NumberedLang = 'Lang' + CAST(ROW_NUMBER() OVER 
               (PARTITION BY P_ID ORDER BY Lang) AS NVARCHAR(100))               
    FROM   YourTableName   
) AS OrderLangs
PIVOT (MAX(Lang) FOR NumberedLang IN (Lang1, Lang2)) AS PivotedLangs

Here is an SQL Fiddle to show you that it works.

If you don't want to specify all of the different NumberedLang values then you will have to use 'dynamic' pivots where you do all of the code in text based SQL. They can be quite inefficient though due to no query plan available.

XN16
  • 5,679
  • 15
  • 48
  • 72
  • I only have 5 different languages for now, but it will be very useful to use dynamic pivots later on for the next stage. Thanks! – Coconuts Feb 27 '14 at 10:18
0

It's a bit ugly, but you can do something like this: 

select p_id
, max( case when langNum=1 then lang else null end ) as Lang1
, max( case when langNum=2 then lang else null end ) as Lang2
, max( case when langNum=3 then lang else null end ) as Lang3
, max( case when langNum=4 then lang else null end ) as Lang4
, max( case when langNum=5 then lang else null end ) as Lang5
-- ...
from ( 
    select p_id
      , lang
      -- Get a number for each row, starting from 1 for each p_id:
      , row_number() over ( partition by p_id order by lang ) as LangNum
    from theTable
) as x
group by p_id
Rory
  • 40,559
  • 52
  • 175
  • 261