Help with query

• PSB

  SSChampion

  Points: 13195

  More actions

  July 24, 2013 at 2:13 pm

  #280948

  CREATE TABLE #NewTempLogins ( CompanyCode varchar(12),Login VARCHAR(MAX))

  CREATE TABLE #TempLogins ( ID INT IDENTITY(1,1), CompanyCode varchar(12),CompanyName VARCHAR(1000),Login VARCHAR(MAX))

  DECLARE @listStr VARCHAR(MAX)

  INSERT INTO #TempLogins (CompanyCode,CompanyName,Login)

  SELECT 'company1','MyCompany','psb'

  UNION ALL

  SELECT 'company1','MyCompany','lll'

  UNION ALL

  SELECT 'company2','MyCompany2','bbb'

  UNION ALL

  SELECT 'company2','MyCompany2','rrr'

  UNION ALL

  SELECT 'company2','MyCompany2','test1'

  DECLARE @StratTrackCount INT = 1

  DECLARE @TrackCount INT = (SELECT MAX(ID) FROM #TEmpLogins)

  SELECT @TrackCount

  select distinct * from #TEmpLogins order By CompanyCode

  DECLARE @CompanyCode VARCHAR(12)

  DECLARE @login VARCHAR(MAX)

  WHILE (@StratTrackCount <= @TrackCount)

  BEGIN

  DECLARE @MyCursor CURSOR

  SET @MyCursor = CURSOR FOR

  SELECT CompanyCode FROM #TEmpLogins

  OPEN @MyCursor

  FETCH NEXT

  FROM @MyCursor INTO @CompanyCode

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SELECT @listStr = COALESCE(@listStr+',' ,'') + Login

  FROM #TEmpLogins where CompanyCode = @CompanyCode

  INSERT INTO #NewTempLogins (CompanyCode,Login)

  SELECT DISTINCT @CompanyCode,@listStr from #TEmpLogins where CompanyCode = @CompanyCode

  FETCH NEXT

  FROM @MyCursor INTO @CompanyCode

  SET @StratTrackCount = @TrackCount + 1

  END

  CLOSE @MyCursor

  DEALLOCATE @MyCursor

  END

  SELECT distinct * FROM #NewTempLogins order By CompanyCode

  drop table #TEmpLogins

  drop table #NewTempLogins

  I want to display the results like below

  CompanyCode Login

  company1 psb,lll

  company2 bbb,rrr,test1

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 24, 2013 at 2:28 pm

  #1635524

  You don't need a cursor or a cycle. There's an easier, shorter and quicker way to do it.

  You can read about it on this article. http://www.sqlservercentral.com/articles/comma+separated+list/71700/

  SELECT CompanyCode,

  Login = STUFF((

  SELECT ',' + x.Login

  FROM #TEmpLogins x

  WHERE x.CompanyCode = t.CompanyCode

  ORDER BY x.Login

  FOR XML PATH('')),1,1,'')

  FROM #TEmpLogins t

  GROUP BY CompanyCode

  ORDER BY CompanyCode;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• PSB

  SSChampion

  Points: 13195

  More actions

  July 24, 2013 at 2:50 pm

  #1635537

  Thanks That helped.