How to use PIVOT in this case?

• bhushan.bagul

  Mr or Mrs. 500

  Points: 553

  More actions

  October 13, 2010 at 4:13 am

  #227837

  Hi Experts,

  Here we go. I have below DDL,DML and what I expecting.

  create table langdtl(id int,speak_flag int,read_flag int,ia_flag int,lit1_flag int,lit2_flag int,lit3_flag int,lang nvarchar(2))

  insert into langdtl values(1,1,1,1,1,1,0,'fi')

  insert into langdtl values(1,1,1,0,0,0,1,'sv')

  insert into langdtl values(1,1,1,1,0,1,0,'en')

  insert into langdtl values(1,1,1,0,1,1,1,'de')

  insert into langdtl values(2,1,1,0,0,1,1,'fi')

  insert into langdtl values(2,1,0,1,1,1,0,'sv')

  insert into langdtl values(2,1,1,0,1,0,1,'en')

  select * from langdtl

  I need output data as shown below

  id speak_flag read_flag ia_flag lit1_flag lit2_flag lit3_flag

  -------------------------------------------------------------------------

  1 de,en,fi,sv de,en,fi,sv en,fi de,fi de,en,fi de,sv

  2 de,en,fi,sv en,fi sv en,sv fi,sv en,fi

  lang should have to display in alphabetical order separated by comma.

  Thanks!

  Bhushan

• WayneS

  SSC Guru

  Points: 95460

  More actions

  October 13, 2010 at 7:22 am

  #1236179

  Does this meet your requirements?

  SELECT DISTINCT

  id,

  speak_flag = STUFF((SELECT ',' + lang

  FROM langdtl

  WHERE id = l.id

  AND speak_flag = 1

  ORDER BY lang

  FOR XML PATH(''),TYPE).value('.', 'varchar(max)'),1,1,''),

  read_flag = STUFF((SELECT ',' + lang

  FROM langdtl

  WHERE id = l.id

  AND read_flag = 1

  ORDER BY lang

  FOR XML PATH(''),TYPE).value('.', 'varchar(max)'),1,1,''),

  ia_flag = STUFF((SELECT ',' + lang

  FROM langdtl

  WHERE id = l.id

  AND ia_flag = 1

  ORDER BY lang

  FOR XML PATH(''),TYPE).value('.', 'varchar(max)'),1,1,''),

  lit1_flag = STUFF((SELECT ',' + lang

  FROM langdtl

  WHERE id = l.id

  AND lit1_flag = 1

  ORDER BY lang

  FOR XML PATH(''),TYPE).value('.', 'varchar(max)'),1,1,''),

  lit2_flag = STUFF((SELECT ',' + lang

  FROM langdtl

  WHERE id = l.id

  AND lit2_flag = 1

  ORDER BY lang

  FOR XML PATH(''),TYPE).value('.', 'varchar(max)'),1,1,''),

  lit3_flag = STUFF((SELECT ',' + lang

  FROM langdtl

  WHERE id = l.id

  AND lit3_flag = 1

  ORDER BY lang

  FOR XML PATH(''),TYPE).value('.', 'varchar(max)'),1,1,'')

  FROM langdtl l

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 13, 2010 at 8:00 am

  #1236216

  Heh you beat me to it Wayne, and yours is nicer too.

  SELECT d.ID,

  speak_flag = REPLACE(iTVF1.RowsToString + '$', ',$', ''),

  read_flag = REPLACE(iTVF1.RowsToString + '$', ',$', ''),

  ia_flag = REPLACE(iTVF3.RowsToString + '$', ',$', ''),

  lit1_flag = REPLACE(iTVF4.RowsToString + '$', ',$', '')

  -- etc etc

  FROM (SELECT ID FROM langdtl GROUP BY ID) d

  CROSS APPLY (SELECT lang + ','

  FROM langdtl WHERE ID = d.ID AND speak_flag = 1

  FOR XML PATH('')) iTVF1 (RowsToString)

  CROSS APPLY (SELECT lang + ','

  FROM langdtl WHERE ID = d.ID AND read_flag = 1

  FOR XML PATH('')) iTVF2 (RowsToString)

  CROSS APPLY (SELECT lang + ','

  FROM langdtl WHERE ID = d.ID AND ia_flag = 1

  FOR XML PATH('')) iTVF3 (RowsToString)

  CROSS APPLY (SELECT lang + ','

  FROM langdtl WHERE ID = d.ID AND lit1_flag = 1

  FOR XML PATH('')) iTVF4 (RowsToString)

  -- etc etc

  ORDER BY ID

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• bhushan.bagul

  Mr or Mrs. 500

  Points: 553

  More actions

  October 13, 2010 at 8:02 am

  #1236219

  thanks for your reply!!!

• WayneS

  SSC Guru

  Points: 95460

  More actions

  October 13, 2010 at 9:58 am

  #1236333

  bhushan.bagul (10/13/2010)

  ---

  thanks for your reply!!!

  Glad to be of help. Does it work right for you?

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  October 24, 2010 at 8:00 pm

  #1241397

  WayneS (10/13/2010)

  ---

  bhushan.bagul (10/13/2010)

  ---

  thanks for your reply!!!

  Glad to be of help. Does it work right for you?

  Heh... don'cha just love it?

  --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)
  

• WayneS

  SSC Guru

  Points: 95460

  More actions

  October 24, 2010 at 8:29 pm

  #1241399

  Jeff Moden (10/24/2010)

  ---

  WayneS (10/13/2010)

  ---

  bhushan.bagul (10/13/2010)

  ---

  thanks for your reply!!!

  Glad to be of help. Does it work right for you?

  Heh... don'cha just love it?

  Exercising my brain: yes.

  Lack of feedback: no.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2