Pivot or dynamic query

• dva2007

  SSCertifiable

  Points: 7707

  More actions

  April 23, 2012 at 12:57 am

  #273085

  hi friends,

  just wondering if some one can help me writing this query?

  drop table #temp

  create table #temp (branch varchar(20), branch_no varchar(5), client varchar(100))

  insert into #temp

  select 'LA', '21200', '11212132'

  union

  select 'LA', '21200', '11213434'

  union

  select 'LA', '21200', '11213444'

  union

  select 'MA', '21201', '23233333+'

  union

  select 'MA', '21201', '11212132red'

  union

  select 'MA', '21201', '11212132green'

  union

  select 'MA', '21201', '11213132green'

  select * from #temp

  I want to group the branch and put client in one row.

  branch branch noclient 1 client 2 client 3 client 4

  LA21200 11212132 11213434 11213444

  MA21201 11212132green11212132red11213132green23233333+

  i dont know how many clients would be there in one branch.

  thanks.

• Rohit Khare

  Mr or Mrs. 500

  Points: 556

  More actions

  April 23, 2012 at 2:02 am

  #1477326

  Refer to these article:

  Using SQL Server 2005/2008 Pivot on Unknown Number of Columns (Dynamic Pivot)[/url]

  SQL Server Pivot: Converting Rows to Columns with Dynamic Query

  Retrieving rows as columns using a single T-SQL query.

  ======================================
  Blog: www.irohitable.com

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  April 25, 2012 at 8:37 pm

  #1479448

  Just out of curiosity, do you know the MAXIMUM number of clients for any branch? At some point you are going to run up against an SQL-imposed limit.

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills