manipulate multiple row data into single row concatinated

• sidcharming

  Newbie

  Points: 5

  More actions

  June 10, 2016 at 8:53 am

  #311176

  I want to consolidate the records from the following table into a single record per PolNo, Year, Platform, Number.

  PolNoYEAR PlatformNumberRecordmemo

  ==== ==== ======================

  1232010pc11The user had issues

  1232010pc12with the os.

  1232009pc21Replaced RAM

  1232010mac11Ordered new CDs

  5642009mac11Broken CD TRAY

  5642010mac11USB port dead

  5642010pc11Ordered replacement

  5642010pc12laptop

  The records will be consolidated into a single record (Record Column will not carry forward). Also at the same time the PolNo, Year, Platform and Number are concatenated into an ID rather than individual columns.

  IDMEMO

  ===================

  123-2010-pc-1The user had issues with the os.

  123-2009-pc-2Replaced RAM

  123-2010-mac-1Ordered new CDs

  564-2009-mac-1Broken CD TRAY

  564-2010-mac-1USB port dead

  564-2010-pc-1Ordered replacement laptop

  As you can see I joined the records in line 1 and line 6 above into one continuous memo field. I however have some memo fields that have 21 or 22 records to combine/join.

  Not sure how I am going to make this happen.

  Thinking cursor, but I don't have much experience and hear it's not effective. The table has around 64k rows to manipulate (which 22k of those have more than one record)

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  June 10, 2016 at 9:34 am

  #1884427

  You can concatenate using 'FOR XML PATH...'

  DECLARE @mytable TABLE(PolNo INT, [YEAR] INT, Platform VARCHAR(3), Number INT, Record INT, memo VARCHAR(30))

  INSERT INTO @mytable(PolNo, [YEAR], Platform, Number, Record, memo)

  VALUES

  (123,2010,'pc',1,1,'The user had issues'),

  (123,2010,'pc',1,2,'with the os.'),

  (123,2009,'pc',2,1,'Replaced RAM'),

  (123,2010,'mac',1,1,'Ordered new CDs'),

  (564,2009,'mac',1,1,'Broken CD TRAY'),

  (564,2010,'mac',1,1,'USB port dead'),

  (564,2010,'pc',1,1,'Ordered replacement'),

  (564,2010,'pc',1,2,'laptop');

  SELECT CAST(t.PolNo AS VARCHAR(10)) + '-' + CAST(t.[YEAR] AS VARCHAR(10)) + '-' + t.Platform + '-' + CAST(t.Number AS VARCHAR(10)) AS ID,

  (SELECT t2.memo + ' ' AS "text()"

  FROM @mytable t2

  WHERE t2.PolNo = t.PolNo

  AND t2.[YEAR] = t.[YEAR]

  AND t2.Platform = t.Platform

  AND t2.Number = t.Number

  ORDER BY t2.Record

  FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(8000)') AS MEMO

  FROM @mytable t

  GROUP BY t.PolNo, t.[YEAR],t.Platform, t.Number

  ORDER BY ID;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537