query help

• surma.sql

  SSCommitted

  Points: 1552

  More actions

  September 4, 2012 at 10:59 am

  #267257

  hello friends

  I have a task

  in that i need to display the values as comma separated in column.

  here is DML

  create table #temp

  (SchooName varchar(12),

  GradeCode int)

  insert into #temp values ('SchoolName', 1)

  insert into #temp values ('SchoolName', 2)

  insert into #temp values ('SchoolName', 3)

  insert into #temp values ('SchoolName', 4)

  insert into #temp values ('SchoolName', 5)

  Need to display like

  Name GradeCode

  SchoolName 1,2,3,4,5

  please help me to do this

  Thanks

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 4, 2012 at 11:21 am

  #1533422

  Give this a try. Any questions, please ask.

  create table #temp

  (SchoolName varchar(12),

  GradeCode int);

  insert into #temp values ('SchoolName', 1);

  insert into #temp values ('SchoolName', 2);

  insert into #temp values ('SchoolName', 3);

  insert into #temp values ('SchoolName', 4);

  insert into #temp values ('SchoolName', 5);

  go

  WITH BaseData AS

  (

  SELECT DISTINCT

  SchoolName

  FROM

  #temp

  )

  SELECT bd.SchoolName,

  GradeCode = STUFF((

  SELECT ',' + cast(t.GradeCode as varchar(12))

  FROM #temp t

  WHERE t.SchoolName = bd.SchoolName

  ORDER BY t.GradeCode

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

  FROM

  BaseData bd

  ORDER BY

  bd.SchoolName;

  go

  drop table #temp;

  go

• RP_DBA

  SSCertifiable

  Points: 5116

  More actions

  September 4, 2012 at 11:22 am

  #1533423

  Not elegant but might prove useful.

  create table #temp

  (SchooName varchar(12),

  GradeCode int)

  insert into #temp values ('SchoolName', 1)

  insert into #temp values ('SchoolName', 2)

  insert into #temp values ('SchoolName', 3)

  insert into #temp values ('SchoolName', 4)

  insert into #temp values ('SchoolName', 5)

  DECLARE @SchooName VARCHAR(12)

  , @GradeCode VARCHAR(MAX);

  SET @SchooName = 'SchoolName'

  SET @GradeCode = '';

  SELECT@GradeCode = @GradeCode + ISNULL(CONVERT(VARCHAR,GradeCode),'NULL') + ','

  FROM#temp

  WHERESchooName = 'SchoolName';

  SELECT @SchooName, LEFT(@GradeCode,LEN(@GradeCode)-1);

  _____________________________________________________________________
  - Nate

  @nate_hughes