How to achieve?

• Maramor

  SSC-Addicted

  Points: 423

  More actions

  March 9, 2012 at 8:47 am

  #258381

  I have the below data in a select statement.

  RealKBServerName

  KB2518870server1

  KB2518870server2

  KB2518870server3

  KB2518870server4

  KB2518870server5

  KB2518870server6

  KB2518870server7

  KB2533523server1

  KB2533523server3

  KB2533523server4

  KB2533523server5

  KB2533523server6

  KB2533523server7

  KB2535512server8

  I want it to look like this?

  RealKBServerName

  KB2518870server1server2server3server4server5server6server7

  KB2533523server1server3server4server5server6server7

  KB2535512server8

  Anyone know how I might "Pivot" this data to get this result? The columns are not set to a particular server just the next one.

  Basically instead of repeating the KB, I just want to list all the servers for that KB.

  I apologize if this is confusing.

• Mr. Holio

  Hall of Fame

  Points: 3328

  More actions

  March 9, 2012 at 9:06 am

  #1457253

  SELECT DISTINCT

  A

  ,(SELECT t2.B + ' '

  FROM (SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS rownum FROM #tbl) t2

  WHERE t2.A = T.A

  FOR XML PATH('')) AS B

  FROM #tbl T

  Of course this would be a distinct list..edited

  __________________________
  Allzu viel ist ungesund...

• Maramor

  SSC-Addicted

  Points: 423

  More actions

  March 9, 2012 at 11:18 am

  #1457315

  Here is my final solution, The one issue I had was it still repeated x number of rows so slapping a DISTINCT solved that. What does the XML do for you here?

  SELECT DISTINCT WindowsVersion, RealKB

  ,(SELECT u2.ServerName + ' '

  FROM (SELECT RealKB, ServerName, ROW_NUMBER() OVER (PARTITION BY RealKB ORDER BY RealKB) AS rownum FROM Updates) U2

  WHERE U2.REalKB = U.RealKB

  FOR XML PATH('')) AS ServerName

  FROM Updates U