pivot help

• jondy87

  SSC-Addicted

  Points: 426

  More actions

  October 12, 2010 at 7:49 pm

  #97517

  Hi guys,

  i have the following table

  TABLE

  N_1 N_2 N_3 N_4 Modes value

  abcnull bcd cdeamazon321

  acdnull bcd cdecircuits421

  acdnull bcd cdecircuits221

  abcnull bcd fegamazon234

  abcnull bcd cdebetcel231

  abcnull bcd cdecetcom521

  abcdef null yuhgetcel 731

  I need the output as

  N_1 N_2 N_3 N_4 amazon circuits betcel cetcom getcel

  abcnull bcdcde 321 ----------------------------------

  acdnull bcdcde ------ 421,221------------------------

  abcnull bcdfeg 234------------------------------------

  abcnull bcdcde -------------- 231----------------

  abcnull bcdcde ---------------------- 521---------

  abcdef nullyuh ------------------------------ 731

  Am working on Pivot but got to convert only 'distinct' modes as columns

  am not sure of other way of working on it .

  Also if all first five columns match then only one row is pulled out having value separating by comma(as you can see in 2nd row in the output)

  Any ideas would greatly help.

  thanks,

  jon

• WayneS

  SSC Guru

  Points: 95460

  More actions

  October 12, 2010 at 8:49 pm

  #1235962

  Please check out the two articles in my signature: CrossTabs and Pivot Tables, Parts 1 and 2. This should give you all the information you need for how to do this.

  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