Pivot to 1 column

• stricknyn

  SSCertifiable

  Points: 6560

  More actions

  February 26, 2010 at 8:00 am

  #145525

  Hello, not sure if this can be done in T-SQL, but is there way to pivot data to one column? ie:

  Here is data from a SQL result for 2 enrollees:

  EnrolleeID, LanguageID, LanguageName

  20, 1, English

  20, 2, Spanish

  20, 3, Portugese

  5, 1, English

  5, 2, Spanish

  I would like the language name to be in one column in my select statement so the results look something like:

  EnrolleeID, HouseholdLanguages

  20, English-Spanish-Portugese

  5, English-Spanish

  Can something like this be done? Also note that I know this can be done if I create a stored procedure and do some looping, but I am trying to avoid that and do it in a select statement.

  Thanks,

  Strick

• Mike01

  SSChampion

  Points: 11395

  More actions

  February 26, 2010 at 10:53 am

  #1125723

  try a CTE

  CREATE TABLE [dbo].[test](

  [EnrolleeID] [int] NULL,

  [LanguageID] [int] NULL,

  [LanguageName] [varchar](20) NULL

  )

  GO

  INSERT INTO [dbo].[test]([EnrolleeID], [LanguageID], [LanguageName])

  SELECT 20, 1, N'English' UNION ALL

  SELECT 20, 2, N'Spanish' UNION ALL

  SELECT 20, 3, N'Portugese' UNION ALL

  SELECT 5, 1, N'English' UNION ALL

  SELECT 5, 2, N'Spanish'

  go

  ;with cte_1 (EnrolleeID,LanguageName,languageID)

  as (select EnrolleeID, cast(LanguageName as varchar(256)), languageID

  from test where languageID = 1

  union all

  select c.EnrolleeID,

  cast(c.LanguageName + Case when t.LanguageName is not null then '-' + t.LanguageName else '' end as varchar(256)),

  t.languageID

  from test t inner join cte_1 c

  on c.EnrolleeID = t.EnrolleeID

  and c.LanguageID = t.LanguageID - 1

  where t.languageID <> 1)

  select EnrolleeID, max(LanguageName) LanguageName

  from cte_1 CurrRow

  group by EnrolleeID

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• LutzM

  SSC Guru

  Points: 107049

  More actions

  February 26, 2010 at 11:17 am

  #1125737

  A recursive cte will cause performance issues on larger data sets.

  A common way to resolve this issue is the usage of FOR XML PATH.

  Something like

  SELECT [EnrolleeID],

  STUFF(

  (SELECT '-' + LanguageName

  FROM #test t2

  WHERE t1.[EnrolleeID]=t2.[EnrolleeID]

  FOR XML PATH('')

  ),

  1,

  1,''

  ) AS t

  FROM #test t1

  GROUP BY [EnrolleeID]

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  February 26, 2010 at 8:37 pm

  #1125919

  lmu92 (2/26/2010)

  ---

  A recursive cte will cause performance issues on larger data sets.

  A common way to resolve this issue is the usage of FOR XML PATH.

  Something like

  SELECT [EnrolleeID],

  STUFF(

  (SELECT '-' + LanguageName

  FROM #test t2

  WHERE t1.[EnrolleeID]=t2.[EnrolleeID]

  FOR XML PATH('')

  ),

  1,

  1,''

  ) AS t

  FROM #test t1

  GROUP BY [EnrolleeID]

  Agreed.... and this isn't a "Pivot"... this is "concatenation".

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Paul White

  SSC Guru

  Points: 150467

  More actions

  February 26, 2010 at 10:31 pm

  #1125971

  lmu92 (2/26/2010)

  ---

  A recursive cte will cause performance issues on larger data sets.

  A common way to resolve this issue is the usage of FOR XML PATH...

  Just to refine that a little, to:

• Give a defined order to the concatenated strings
• Overcome problems with XML entitization
• Produce a faster query plan
• Order the final results

Try:

DECLARE @Result

TABLE (

enrollee_id INTEGER NOT NULL,

language_id INTEGER NOT NULL,

language_name NVARCHAR(50) NOT NULL

)

INSERT @Result (enrollee_id, language_id, language_name)

VALUES (20, 1, N'English');

INSERT @Result (enrollee_id, language_id, language_name)

VALUES (20, 2, N'Spanish');

INSERT @Result (enrollee_id, language_id, language_name)

VALUES (20, 3, N'Portugese');

INSERT @Result (enrollee_id, language_id, language_name)

VALUES (5, 1, N'English');

INSERT @Result (enrollee_id, language_id, language_name)

VALUES (5, 2, N'Spanish');

SELECT R1.enrollee_id,

languages =

STUFF

(

(

SELECT N'-' + R2.language_name

FROM @Result R2

WHERE R2.enrollee_id = R1.enrollee_id

ORDER BY

R2.language_name ASC

FOR XML PATH(''), TYPE

).value('./text()[1]', 'NVARCHAR(MAX)')

, 1, 1, N'')

FROM @Result R1

GROUP BY

R1.enrollee_id

ORDER BY

R1.enrollee_id ASC;

Results:

enrollee_id languages

5 English-Spanish

20 English-Portugese-Spanish

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi